with Concatenated field, am not able to perform lookup..

[bharani8]

Hi Experts.. Need your quick input on my below query..

I have 2 tables in my script..

In the first table.. i have concatenated 2 fields as shown below

Table name - CSV_FILE:

     SO_ID,

     SO_LN_ITM_ID,

     SHIP_QT,

     SO_ID&SO_LN_ITM_ID as [CSV Concat],

In my 2nd Table.. i have done this..

Table name-India:

     SAPOrdNo,

      Itemno,

     SAPOrdNo & Itemno as [Fusion concat],

Now when i try to do a lookup to bring the corresponding Qty (SHIP_QT) from first table.. i end up getting an error saying "[Fusion concat] is not available"

Lookup('SHIP_QT','CSV_Concat',[Fusion concat],'CSV_FILE') as [Vlookup value]

Please help..

[ciaran_mcgowan]

The syntax look correct but in the Loopkup function, you are referencing 'CSV_Concat' but in the table, the field is called [CSV Concat].

Not sure if that's what's causing your issue.

[bharani8]

Hi McGowan - That was typo from my end.. it is same in my coding

[ciaran_mcgowan · Accepted solution]

Cool. I've had a look at this with some dummy data and it looks like LOOKUP() doesn't use the value you created when loading the table as it does not exist in the table you are reading FROM. So you need to use the formula you used to create [Fusion concat].

All you have to do to make this work is change:

Lookup('SHIP_QT','CSV_Concat',[Fusion concat],'CSV_FILE') as [Vlookup value]

to

Lookup('SHIP_QT', 'CSV_Concat', SAPOrdNo & Itemno, 'CSV_FILE') as [Vlookup value]

[bharani8]

Thanks a ton McGowan!! U are an Star!!