Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am loading in a data file that contains a receipt number. This number is duplicated against multiple lines. To try to speed up calculations i am looking to populate a new column during the load with a 1 on the first instance of a new receipt number and a 0 on all duplicate lines.
I know I can do this via count(distinct in expressions but want to leverage to speed of sum by utilising 1 and 0 's.
Many thanks
Mark
Maybe like
LOAD
ReceiptNr,
if( exists(ReceiptNr),0,1) as FirstOccurrence,
...
FROM ...;
Maybe like
LOAD
ReceiptNr,
if( exists(ReceiptNr),0,1) as FirstOccurrence,
...
FROM ...;
Hi Mark,
Your load script should look something like:
TempTable:
LOAD If(exists([Recept ID]),0,1),
ID,
[Recept ID],
Amount
FROM
[Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);
This will hopefully produce a table as you desire
Cheers,
Ravi