Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);