Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.
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!
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