Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can anyone please tell me why the following doesn't seem to work - It doesn't like my preceeding load, everything else is okay.
Thanks!!
LOAD *, if([DATE] = max([DATE]),1,0) as current;
LOAD [Product ID],
[Plan Status],
Activity,
[Plan Age],
Dormancy,
SIOs,
Date(FileDate, 'DD-MM-YYYY') AS [DATE],
FileDate,
Revenue,
month([FileDate])& '-' & year([FileDate]) as [Month]
FROM
[\\sample_data.xlsx]
(ooxml, embedded labels, table is [Plan Detail]);
Hi,
There is no from in the first load!
Hi
You can't get an aggregate value (like Max) and the detail in the same query - so you will need to break this into steps:
Data:
LOAD [Product ID],
[Plan Status],
Activity,
[Plan Age],
Dormancy,
SIOs,
Date(FileDate, 'DD-MM-YYYY') AS [DATE],
FileDate,
Revenue,
month([FileDate])& '-' & year([FileDate]) as [Month]
FROM [\\sample_data.xlsx]
(ooxml, embedded labels, table is [Plan Detail]);
MaxDate:
LOAD Max(DATE) As maxDate
Resident Data;
Let vmaxDate = Num(Peek('maxDate'));
DROP TABLE MaxDate;
Join(Data)
LOAD Distinct DATE,
If(DATE = $(maxDate), 1, 0) As current
Resident Data;
HTH
Jonathan
Hi,
It does not work as per my knowledge.
Regards,
Nagarjuna
MaxDate:
LOAD Max(DATE) As maxDate
Resident Data;
Let vmaxDate = Num(Peek('maxDate'));
DROP TABLE MaxDate;
LOAD *, if([DATE] = Date('$(vmaxDate)','DD-MM-YYYY'),1,0) as current;
Data:
LOAD [Product ID],
[Plan Status],
Activity,
[Plan Age],
Dormancy,
SIOs,
Date(FileDate, 'DD-MM-YYYY') AS [DATE],
FileDate,
Revenue,
month([FileDate])& '-' & year([FileDate]) as [Month]
FROM
[\\sample_data.xlsx]
(ooxml, embedded labels, table is [Plan Detail]);
Hi Jonathan,
Thanks.. this looks good but the join section doesn't work.
Also, why do we need to create the maxDate and then use a variable as well? What does the num(peek) do?