topic Re: Max sales date in LoadCript in QlikView

Max sales date in LoadCript

Anonymous — Fri, 08 Aug 2014 09:00:57 GMT

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!

Re: Max sales date in LoadCript

tresB — Fri, 08 Aug 2014 09:10:41 GMT

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

Re: Max sales date in LoadCript

jagan — Fri, 08 Aug 2014 09:53:37 GMT

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.

Re: Max sales date in LoadCript

Anonymous — Fri, 08 Aug 2014 09:59:49 GMT

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!

Re: Re: Max sales date in LoadCript

Anonymous — Fri, 08 Aug 2014 10:14:33 GMT

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