Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
m_lavova
Contributor
Contributor

condition in load script

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

 

 

2 Solutions

Accepted Solutions
sunny_talwar

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
,

View solution in original post

sunny_talwar

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

Best,
Sunny

View solution in original post

8 Replies
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
Contributor
Contributor
Author

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
Contributor
Contributor
Author

Still same 😞

sunny_talwar

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
Contributor
Contributor
Author

You are great, it work perfectly 🙂

Thank you sooooo much

sunny_talwar

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

Best,
Sunny

Sue_Macaluso
Community Manager
Community Manager

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