topic Re: Help with preceeding load please in QlikView

Help with preceeding load please

Fri, 24 Apr 2015 05:53:56 GMT

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

Re: Help with preceeding load please

Marcellino_Groothof — Fri, 24 Apr 2015 05:57:52 GMT

Hi,

There is no from in the first load!

Re: Help with preceeding load please

jonathandienst — Fri, 24 Apr 2015 06:00:07 GMT

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

Re: Help with preceeding load please

nagarjuna_kotha — Fri, 24 Apr 2015 06:10:58 GMT

Hi,

It does not work as per my knowledge.

Regards,

Nagarjuna

Re: Help with preceeding load please

anbu1984 — Fri, 24 Apr 2015 06:15:34 GMT

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

Re: Help with preceeding load please

Fri, 24 Apr 2015 06:15:38 GMT

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?