condition in load script

[m_lavova]

Dears,

is there someone, who can help me?

I created this script :
[SAP-BW-data]:
LOAD
...
Lookup('CONTAINER','MATERIAL_CODE',[Material (IC)], 'PUMAS')as "Lieferant_PUMAS",
Lookup('company','Material',[Material (IC)], 'EDOS')as "Lieferant_EDOS",
IF ([Lieferant_PUMAS] = ''    OR     [Lieferant_PUMAS] = '_No_Container_Available', [Lieferant_EDOS], [Lieferant_PUMAS]) AS Lieferant,
...

Lookup conditions work well, but condition with if is not good - during load data I got error message.

I need to create "Lieferant" (something like new column).
Data in it will come from Lieferant_PUMAS, but IF Lieferant_PUMAS is empty or contains '_No_Container_Available', then data will be taken from Lieferant_EDOS, otherwise data will comes from Lieferant_PUMAS.

How I can correct my scrip in correct version?

Please advise me...
thank you,
Monika

[sunny_talwar]

There is two ways to fix this...

1) Use a preceding load to create Lieferant field

[SAP-BW-data]:
LOAD *,
IF ([Lieferant_PUMAS] = ''    OR     [Lieferant_PUMAS] = '_No_Container_Available', [Lieferant_EDOS], [Lieferant_PUMAS]) AS Lieferant;
LOAD
...
Lookup('CONTAINER','MATERIAL_CODE',[Material (IC)], 'PUMAS')as "Lieferant_PUMAS",
Lookup('company','Material',[Material (IC)], 'EDOS')as "Lieferant_EDOS",

...

2) Since the field is going to be available after the load is completed, use the definition you use to create it

[SAP-BW-data]:
LOAD
...
Lookup('CONTAINER','MATERIAL_CODE',[Material (IC)], 'PUMAS') as "Lieferant_PUMAS",
Lookup('company','Material',[Material (IC)], 'EDOS') as "Lieferant_EDOS",
IF ([Lieferant_PUMAS] = ''    OR     [Lieferant_PUMAS] = '_No_Container_Available',
Lookup('company','Material',[Material (IC)], 'EDOS'),
Lookup('CONTAINER','MATERIAL_CODE',[Material (IC)], 'PUMAS')) AS Lieferant,
...

[m_lavova]

Thank you,

but still I have this message:

The following error occurred:

Field 'Lieferant_PUMAS' not found

[sunny_talwar]

My bad, the second one should look like this

[SAP-BW-data]:
LOAD
...
Lookup('CONTAINER','MATERIAL_CODE',[Material (IC)], 'PUMAS') as "Lieferant_PUMAS",
Lookup('company','Material',[Material (IC)], 'EDOS') as "Lieferant_EDOS",
IF (
Lookup('CONTAINER','MATERIAL_CODE',[Material (IC)], 'PUMAS') = '' OR
Lookup('CONTAINER','MATERIAL_CODE',[Material (IC)], 'PUMAS') = '_No_Container_Available',
Lookup('company','Material',[Material (IC)], 'EDOS'),
Lookup('CONTAINER','MATERIAL_CODE',[Material (IC)], 'PUMAS')
) AS Lieferant,

[m_lavova]

Still same 😞

[sunny_talwar · Accepted solution]

But I don't even have any reference of [Lieferant_PUMAS] or [Lieferant_EDOS] in the new script I shared... do you know where the error is getting generated? Or do you have another place in the same load where you use it?

[SAP-BW-data]:
LOAD
...
Lookup('CONTAINER','MATERIAL_CODE',[Material (IC)], 'PUMAS') as "Lieferant_PUMAS",
Lookup('company','Material',[Material (IC)], 'EDOS') as "Lieferant_EDOS",
IF (
Lookup('CONTAINER','MATERIAL_CODE',[Material (IC)], 'PUMAS') = '' OR
Lookup('CONTAINER','MATERIAL_CODE',[Material (IC)], 'PUMAS') = '_No_Container_Available',
Lookup('company','Material',[Material (IC)], 'EDOS'),
Lookup('CONTAINER','MATERIAL_CODE',[Material (IC)], 'PUMAS')
) AS Lieferant,

[m_lavova]

You are great, it work perfectly 🙂

Thank you sooooo much

[sunny_talwar · Accepted solution]

Super!! Please close the thread if you got what you wanted.

Best,
Sunny

[Sue_Macaluso]

Is this for QlikView or Qlik Sense? Although answered I would like to move it to the correct product forum