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: 
kunkumnaveen
Specialist
Specialist

Need help in scripting

Hello All,

Need a help in creating a Flag column at script with values 1,0.

If for a combination of MC,VC,PC if VF is >= 01-09-2019(DD-MM-YYYY),

then flag 1 should be assign else 0.

For example the first row in the below table should get flag 0 because valid from is not >= 01-09-2019

Output required

 
 

Capture.PNG

Please find the sample data attachment and required output pic

1 Solution

Accepted Solutions
zhadrakas
Specialist II
Specialist II

that should work:

SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';
LET vValidFrom = date('01-09-2019');

SOURCE:
Load *, if(date(VF) >= '$(vValidFrom)', 1,0) as Flag
;
LOAD MC, 
     VC, 
     PC, 
     max(VF) as VF
FROM [C:\tmp\TEST.xlsx] (ooxml, embedded labels, table is Sheet1)
GROUP BY MC, VC, PC;

drop field VF from SOURCE;

left join 
load MC, 
     VC, 
     PC, 
     VF
FROM [C:\tmp\TEST.xlsx] (ooxml, embedded labels, table is Sheet1);

View solution in original post

1 Reply
zhadrakas
Specialist II
Specialist II

that should work:

SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';
LET vValidFrom = date('01-09-2019');

SOURCE:
Load *, if(date(VF) >= '$(vValidFrom)', 1,0) as Flag
;
LOAD MC, 
     VC, 
     PC, 
     max(VF) as VF
FROM [C:\tmp\TEST.xlsx] (ooxml, embedded labels, table is Sheet1)
GROUP BY MC, VC, PC;

drop field VF from SOURCE;

left join 
load MC, 
     VC, 
     PC, 
     VF
FROM [C:\tmp\TEST.xlsx] (ooxml, embedded labels, table is Sheet1);