Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with preceeding load please

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

5 Replies
Contributor III
Contributor III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner
Partner

Hi,

It does not work as per my knowledge.

Regards,

Nagarjuna

Master III
Master III

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

Not applicable

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?