how to use qlikview function in qlikview using Preceding Load

[#1]

Hi Everyone,

I need Help to understand Preceding Load..  i want to find out max Date but using Preceding load.. i dont want to use Resident Load

Date2:

Load *,

max(OrderDate) as MaxDate;

LOAD

  date(Floor(OrderDate)) as OrderDate,

Date(OrderDate) as MasterDate,

Day(OrderDate),

Month(OrderDate),

Year(OrderDate)

FROM

(ooxml, embedded labels, table is OrderHeader);

is it a correct syntax to use preceding load .. Because i m getting an Error.. as well what is the Best way to use Preceding Load..

and In which Condition we should Use only Preceding Load not a Resident Load

[pokassov]

Hello!

You use '*'. It means all fields in preceding load. Then you have to use group by in second load with your fields.

If you need only MaxDate:

Load

max(OrderDate) as MaxDate;

LOAD

  date(Floor(OrderDate)) as OrderDate,

Date(OrderDate) as MasterDate,

Day(OrderDate),

Month(OrderDate),

Year(OrderDate)

FROM

(ooxml, embedded labels, table is OrderHeader);

[qlikmsg4u]

If you want to find only max date then you could use like this (Remove *),

Date2:

Load

max(OrderDate) as MaxDate;

LOAD

  date(Floor(OrderDate)) as OrderDate,

Date(OrderDate) as MasterDate,

Day(OrderDate),

Month(OrderDate),

Year(OrderDate)

FROM

(ooxml, embedded labels, table is OrderHeader);

[jonathandienst]

Unfortunately that will not work - you cannot return detail and an aggregate in the same load - with or without preceding load. The only way is to perform a resident load or load from the source data again using a Max() and a Group By statement.

[maxgro]

Jonathan, in 11 SR10 works

Date2:

LOAD

     max(OrderDate) as MaxDate;

LOAD

  date(Floor(OrderDate)) as OrderDate,

Date(OrderDate) as MasterDate,

Day(OrderDate),

Month(OrderDate),

Year(OrderDate)

FROM

(ooxml, embedded labels, table is OrderHeader);

and also with a group by in the preceding load and a where

Date2:

Load

  Year,

  max(OrderDate) as MaxDate

Where Year >= 2014

group by Year;

LOAD

  date(Floor(OrderDate)) as OrderDate,

  Date(OrderDate) as MasterDate,

  Day(OrderDate),

  Month(OrderDate),

  Year(OrderDate) as Year

FROM

[Sales Multi Table.xlsx]

(ooxml, embedded labels, table is OrderHeader);

[ganeshreddy]

I agree with you Jonathan, by using preceding load he can only get a maximum value but he can not filter it by Day, month and year, i suppose.

[jonathandienst]

Those work, but they only return the aggregate (and Group By terms like Year). They do not return the detail AND the aggregate, and this is what I said cannot be done by a single preceding load.

[jagan]

Hi,

Using the Aggregation functions like Sum, Count, Max and Min is not possible in Precedent Load you have to use Group By for this.

Try like below

Temp:

LOAD

  date(Floor(OrderDate)) as OrderDate,

Date(OrderDate) as MasterDate,

Day(OrderDate),

Month(OrderDate),

Year(OrderDate),

Alt(Peek(MaxDate), OrderDate))  AS MaxDate

FROM

(ooxml, embedded labels, table is OrderHeader)

ORDER BY OrderDate Desc;

Hope this helps you.

Regards,

Jagan.

[maxgro]

@jonathan

if the req is to have detail and max I agree with you

Unfortunately that will not work - you cannot return detail and an aggregate in the same load - with or without preceding load. The only way is to perform a resident load or load from the source data again using a Max() and a Group By statement.

@jagan

don't think the order by work from an excel, only from a resident?