topic Re: Max In Date with a group by in load script in QlikView

Max In Date with a group by in load script

chriys1337 — Tue, 24 Oct 2017 07:31:49 GMT

Hi,

I want to find the maximum Date for each Customer (Code) on a specific route (From, To).

Unfortunately the debugger is throwing out an "Invalid Expression" - error.

I don't know what is wrong with my script, any ideas? Thx a lot ! ! ! !

main:
LOAD * INLINE [
Customer, Code, From, To, In, Out, Revenue
DHL, DHL001, Asia, Europe, 201701, 201705, 10
DHL, DHL001, Asia, Europe, 201706, 201710, 20
DHL, DHL001, Africa, Europe, 201707, 201720, 1
HSBC, HSBC002, Asia, Europe, 201708, 201710, 22
];

main2:
NoConcatenate Load *, max(In) as Max_In_Date Resident main group by Code, From, To;

Drop Table main;
!

Re: Max In Date with a group by in load script

srivastalans — Tue, 24 Oct 2017 07:51:12 GMT

Hi,

In main2, Remove * and load only required fields in group by

main2:
NoConcatenate Load Code, From, To, max(In) as Max_In_Date Resident main group by Code, From, To;

Instead of

main2:
NoConcatenate Load *, max(In) as Max_In_Date Resident main group by Code, From, To;

Re: Max In Date with a group by in load script

avinashelite — Tue, 24 Oct 2017 07:57:04 GMT

You cannot use the * on the grouping table...and you need to mention the specific filed names on which you need the grouping

Re: Max In Date with a group by in load script

antoniotiman — Tue, 24 Oct 2017 08:23:02 GMT

Try this

main:
LOAD * INLINE [
Customer, Code, From, To, In, Out, Revenue
DHL, DHL001, Asia, Europe, 201701, 201705, 10
DHL, DHL001, Asia, Europe, 201706, 201710, 20
DHL, DHL001, Africa, Europe, 201707, 201720, 1
HSBC, HSBC002, Asia, Europe, 201708, 201710, 22
];
Left Join
Load Code,From,To,max(In) as Max_In_Date Resident main group by Code, From, To;

Re: Max In Date with a group by in load script

sunny_talwar — Tue, 24 Oct 2017 08:32:55 GMT

Here is another way and may work better with large dataset

main:

LOAD AutoNumber(Code&From&To) as Key,

LOAD * INLINE [

Customer, Code, From, To, In, Out, Revenue

DHL, DHL001, Asia, Europe, 201701, 201705, 10

DHL, DHL001, Asia, Europe, 201706, 201710, 20

DHL, DHL001, Africa, Europe, 201707, 201720, 1

HSBC, HSBC002, Asia, Europe, 201708, 201710, 22

];

main2:

LOAD *,

If(Key = Previous(Key), Peek('Max_In_Date'), In) as Max_In_Date

Resident main

Order By Key, In desc;

Drop Table main;

Re: Max In Date with a group by in load script

chriys1337 — Tue, 24 Oct 2017 13:50:55 GMT

Hi Sunny,

it seem to work, two questions:

If(Key = Previous(Key), Peek('Max_In_Date'), In) as Max_In_Date

What is this doing?

Where do you have this Peek('Max_In_Date') from? Its not obvious for me.

Thx a lot,

Chris

Re: Max In Date with a group by in load script

sunny_talwar — Tue, 24 Oct 2017 14:12:58 GMT

1) If Key is equal to Key in the previous row, then Peek the value of Max_In_Date from previous row... if not, then use In

2) Peek() function is unique in a sense that it can be used in the table where it is getting created... so although Max_In_Date doesn't exists before.... it can still be used within the Peek() function of the same load. the reason to use this is that as a row get populated, peek can look at the newly created value in the previous row.