topic Re: how to use qlikview function in qlikview using Preceding Load in QlikView

how to use qlikview function in qlikview using Preceding Load

Tue, 04 Aug 2015 06:23:13 GMT

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

Re: how to use qlikview function in qlikview using Preceding Load

pokassov — Tue, 04 Aug 2015 06:48:52 GMT

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

Re: how to use qlikview function in qlikview using Preceding Load

qlikmsg4u — Tue, 04 Aug 2015 07:05:42 GMT

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

Re: how to use qlikview function in qlikview using Preceding Load

jonathandienst — Tue, 04 Aug 2015 07:13:20 GMT

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.

Re: how to use qlikview function in qlikview using Preceding Load

maxgro — Tue, 04 Aug 2015 07:25:35 GMT

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

Re: how to use qlikview function in qlikview using Preceding Load

ganeshreddy — Tue, 04 Aug 2015 07:26:24 GMT

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.

Re: how to use qlikview function in qlikview using Preceding Load

jonathandienst — Tue, 04 Aug 2015 07:50:56 GMT

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.

Re: how to use qlikview function in qlikview using Preceding Load

jagan — Tue, 04 Aug 2015 10:17:37 GMT

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.

Re: how to use qlikview function in qlikview using Preceding Load

maxgro — Tue, 04 Aug 2015 10:33:29 GMT

@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?