Need help in scripting

[kunkumnaveen]

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

Please find the sample data attachment and required output pic

[zhadrakas · Accepted solution]

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);