3

In my mapping, I am using flat files as source and target. I have to use unconnected lookup. Can somebody tell me how to return multiple values from unconnected lookup specially when we are using flat files as source and target.

I know how to return multiple values when we use relational tables. In that case, we just concat values and return as single value. Again we split those values.

Please help me.

Vicky
  • 1,215
  • 6
  • 22
  • 45
  • 3
    @Marek-Informatica Unconnected Lookup by default supports only one return port. So alternatively write an lookup sql override with the required ports values concatenated into a single string as return port value. Call the Unconnected lookup from the expression transformation and use various output ports to retrieve the lookup values based on the concatenated return value. Use SUBSTR,INSTR to extract the column values from the concatenated return field. You can see the detail explaination on this link-http://informaticatechtiks.blogspot.in/2012/05/how-to-return-muliple-values-using.html – Vicky Apr 04 '13 at 13:15
  • @Marek- and Yes, i am aware of the setting Return All Values on Multiple Match. i think, that will help when we get multiple match in the same column, right?? Please correct me if i am wrong. – Vicky Apr 04 '13 at 13:15
  • That's right. I thought that by *returning multiple values* you meant a set of rows while in fact you meant columns. – Marek Grzenkowicz Apr 04 '13 at 13:36
  • We can return entire line as a single port by using SQL query(When source is relational table). how is it possible when we are working with flat files? – Vicky Apr 04 '13 at 14:11
  • I was thinking about defining a source definition (and later a lookup) with two port - `key` and `rest_of_line`. Then you could fetch lines by `key` and parse `rest_of_line` values. But this won't work for a delimited file. Can't you load this file into an auxiliary table and then you the unconnected lookup trick you already know? I'm afraid that's the easiest way. – Marek Grzenkowicz Apr 05 '13 at 07:13
  • Maybe I do not understand the question, but you can read entire lines as a single port. Just use the file as if it was a fixed width file (with only a wide first field). But you must then parse all the fields by hand. – momobo Apr 05 '13 at 15:04

2 Answers2

1

if unconnected lookup on relational table

  1. In lookup override we can concatenate two or multiple ports and return that port to expression transformation.
    1. In expression transformation extract those values.
DoOrDie
  • 315
  • 3
  • 12
0

I think you replace the first delimiter with some other delimiter(say &) in the source file. Using "&" as delimiter you can create the lookup and use it to retrieve the concatenated return field which wil give you multiple return values for the match.

Sandeep
  • 36
  • 3