Max sales date in LoadCript

[Anonymous]

Good day!

In pivot table "Max sales date" i calculate max date by Spoints, where quantity > 0

My question: How can i write the same in loadscript to load in one table field Spoint and their Max sales dates (quantity > 0) ?

Thank you!

[tresesco]

Input:
LOAD * INLINE [
    Quantity, Spoint, ProductType, Date
    0,  point 1, type1,21-09-2014
    12, point 2,type2, 22-09-2014
    33, point 3,type3, 23-09-2014
    44, point 5,type4, 22-09-2014
    55, point 6,type1, 21-09-2014
    67, point 7,type5, 24-09-2014
    99, point 8,type7, 25-09-2014
    00, point 9,type3, 26-09-2014
    33, point 4,type4, 21-09-2014
    334, point 10,type1, 22-09-2014
    3, point 1,type1, 21-10-2014
    0, point 2,type1, 22-12-2014
    34, point 3,type1, 23-12-2014
    0, point 5,type1, 22-12-2014
    56, point 6,type1, 21-12-2014
    12, point 7,type3, 24-03-2014
    1, point 8,type1, 25-07-2014
    00, point 9,type1, 26-11-2014
    21, point 4,type1, 21-12-2014
    12, point 10,type1, 23-09-2014
];

Join
Load
Spoint,
Date(Max(Date))
Resident Input Where Quantity>0 Group By Spoint;

PFA

[jagan]

Hi,

Try like this

Data:

LOAD * INLINE [

    Quantity, Spoint, ProductType, Date

    0,  point 1, type1,21-09-2014

    12, point 2,type2, 22-09-2014

    33, point 3,type3, 23-09-2014

    44, point 5,type4, 22-09-2014

    55, point 6,type1, 21-09-2014

    67, point 7,type5, 24-09-2014

    99, point 8,type7, 25-09-2014

    00, point 9,type3, 26-09-2014

    33, point 4,type4, 21-09-2014

    334, point 10,type1, 22-09-2014

    3, point 1,type1, 21-10-2014

    0, point 2,type1, 22-12-2014

    34, point 3,type1, 23-12-2014

    0, point 5,type1, 22-12-2014

    56, point 6,type1, 21-12-2014

    12, point 7,type3, 24-03-2014

    1, point 8,type1, 25-07-2014

    00, point 9,type1, 26-11-2014

    21, point 4,type1, 21-12-2014

    12, point 10,type1, 23-09-2014

];

LEFT JOIN(Data)

Load

Spoint,

Date(Max(Date)) AS MaxDate

Resident Data

Where Quantity>0

Group By Spoint;

Hope this helps you.

Regards,

jagan.

[Anonymous]

ok...

can you write me.

i need to create pivot table with dimensions Spoint and ProductType

i want to get quantity with MaxSalesDate. This date must be different for Spoint but the same for ProductType.

In other words, for example, for Spoint 2 MaxSalesDate is 22.09.2014. I need to get quantity for this point with 22.09.2014 for all ProductTypes

Thank you for help!

[Anonymous]

i wrote expression,

but don't know, why there are now values for point 2, point 5

and values for point 7 and point 8 are not correct. for my data there must be only one value in every point