Help with preceeding load please

[#1]

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

[Marcellino_Groothof]

Hi,

There is no from in the first load!

[jonathandienst]

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

[nagarjuna_kotha]

Hi,

It does not work as per my knowledge.

Regards,

Nagarjuna

[anbu1984]

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

[#6]

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?