Create new column in load script

[KirstenKa]

In the load script I would like to add a new column which check 4 columns to see if there is a value or an empty value, if value in all 4 columns, then “ready” is added in the row, if no value in 1 of the 4 columns then “not ready”

[Oleg_Troyansky · Accepted solution]

Hi Kirsten,

Something like this should work:

LOAD

...

IF(len(trim(F1)) * len(trim(F2)) * len(trim(F3)) * len(trim(F4)) > 0, 'Ready', 'Not Ready') as Status,

...

If any of the fields is empty, then len(trim()) will return 0 and the whole expression is zero. When all fields are filled up, then the multiplication of the 4 lengths will produce a non-zero result.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[KirstenKa]

Thanks, how to integrate this in the load script? If I add it like above it gives an error, see attachment

[Oleg_Troyansky]

You are missing a comma in the previous line (1101).

[KirstenKa]

Thanks. See attachment: Comma’s is helping a bit but still not completely, I added 1  after “Vol. Unit” and 1 after “Sales order” Can this be related to that the different columns have different values, so >0 can not be used for all? I assume I should use a different formula’s for each column

Values in code:

• Theme-> text values,  blanks should be removed
• Distr -> text values,  0 should be removed
• MovAv.4200 -> numbers (with 2 decimals -> 0,00), 0,00 and blanks should be removed

Values not in code yet, due to bracket issue -> code doesn't accept value due to bracket or - in code:

• CoO(Purcha -> text values,  blanks should be removed
• LSRP-4000 -> numbers (with 2 decimals -> 0,00), 0 should be removed

[sidhiq91]

@KirstenKa  Please see the code below:

NoConcatenate
Temp:
Load * Inline [
A,B,C,D
1,5,9,13,
2,6,10,14
,7,11,15
4,8,12,
];

NoConcatenate
Temp1:
Load *,
if(not isnull(emptyisnull(A)) and Not isnull(emptyisnull(B)) and
not isnull(emptyisnull(C)) and not isnull(emptyisnull(D)),'Ready','Not Ready')
as Status
Resident Temp;

Drop table Temp;

Exit Script;

If this resolves your issue, please like and accept it as a solution.

[KirstenKa]

Thanks but in this code I do not see how to relate it to the columns and do not know where the numbers stand for. Is A, B, C, D columns?

and no concatenate temp should be above the load script ? Or full script should be under original Load?

[Oleg_Troyansky · Accepted solution]

Kirsten,

in the formula that you attached in the PDF document, there are more opening parentheses than closing parentheses, and that causes a syntax error. Count your parentheses and correct the formula.

I spotted unnecessary parentheses before the second and the third function len -  those need to be removed. Then, look for any other syntax issues and make sure that you have the same number of opening and closing parentheses and that they are placed logically.

[KirstenKa]

Thanks it works now I removed the parenthesis as you mentioned and the comma at the end. Though I need to see if the end result in the column is now correct because I see some strange numbers in my graph. Will do some investigation and will let you know, once correct I will accept it as a solution. 

[KirstenKa]

@Oleg_Troyansky  the script now works and I can load the data. When I look directly in the datasource itself I see "ready" statuses are generated in the column. However when I make a graph I see zero results. Is there something wrong with how I make the graph? (See attachment)  Or should different expressions being used in the script since I have different data per column, see below

• Theme-> text values,  blanks should be removed
• Distr -> text values,  0 should be removed
• MovAv.4200 -> numbers (with 2 decimals -> 0,00), 0,00 and blanks should be removed
• CoO(Purcha -> text values,  blanks should be removed
• LSRP-4000 -> numbers (with 2 decimals -> 0,00), 0 should be removed