Max In Date with a group by in load script

[chriys1337]

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

[srivastalans]

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;

[avinashelite]

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

[antoniotiman]

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;

[sunny_talwar]

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;

[chriys1337]

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

[sunny_talwar]

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.