Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm facing an issue while trying to create (in the script, not at the GUI) the following:
I have a table with few dimensions and a value field.
I wish to create a new table with incremental value as the new data for each key record (combination of all the dimensions). I'll try to explain with an image. the image shows 2 tables (the left one is the "before" table which I have and the right one is the "after" table which I wish to have).
In the image I've putted a dummy data just for the explanation of my problem (I've highlighted as an example cells which sums into a new cell at the result table).
Please assist.
Thanks and best regards,
Shay
SCRIPT
Directory;
Source:
LOAD YearMonth,
Year,
Month,
CompID,
Item,
Value
FROM
[107297.xlsx]
(ooxml, embedded labels, table is Sheet1);
Table:
NoConcatenate
LOAD YearMonth,
Year,
Month,
CompID,
Item,
Value,
if(Item<>peek(Item) or CompID<>peek(CompID), Value, peek(RollingValue) + Value) as RollingValue
Resident Source
order by Item, CompID, Year, Month;
DROP Table Source;
RESULT
YearMonth | Year | Month | CompID | Item | Value | RollingValue |
2013 | 1 | 111 | A | 1284 | 1284 | |
2013 | 1 | 111 | B | 572 | 572 | |
2013 | 1 | 222 | A | 536 | 536 | |
2013 | 1 | 222 | B | 822 | 822 | |
2013 | 2 | 111 | A | 1167 | 2451 | |
2013 | 2 | 111 | B | 1170 | 1742 | |
2013 | 2 | 222 | A | 780 | 1316 | |
2013 | 2 | 222 | B | 916 | 1738 | |
2013 | 3 | 111 | A | 1157 | 3608 | |
2013 | 3 | 111 | B | 1282 | 3024 | |
2013 | 3 | 222 | A | 1479 | 2795 | |
2013 | 3 | 222 | B | 1269 | 3007 | |
2013 | 4 | 111 | A | 868 | 4476 | |
2013 | 4 | 111 | B | 1003 | 4027 | |
2013 | 4 | 222 | A | 1004 | 3799 | |
2013 | 4 | 222 | B | 773 | 3780 | |
2013 | 5 | 111 | A | 1268 | 5744 | |
2013 | 5 | 111 | B | 669 | 4696 | |
2013 | 5 | 222 | A | 1255 | 5054 | |
2013 | 5 | 222 | B | 1222 | 5002 | |
2013 | 6 | 111 | A | 608 | 6352 | |
2013 | 6 | 111 | B | 1237 | 5933 | |
2013 | 6 | 222 | A | 723 | 5777 | |
2013 | 6 | 222 | B | 715 | 5717 |
could you post your data (excel, txt, ....)?
thanks
SCRIPT
Directory;
Source:
LOAD YearMonth,
Year,
Month,
CompID,
Item,
Value
FROM
[107297.xlsx]
(ooxml, embedded labels, table is Sheet1);
Table:
NoConcatenate
LOAD YearMonth,
Year,
Month,
CompID,
Item,
Value,
if(Item<>peek(Item) or CompID<>peek(CompID), Value, peek(RollingValue) + Value) as RollingValue
Resident Source
order by Item, CompID, Year, Month;
DROP Table Source;
RESULT
YearMonth | Year | Month | CompID | Item | Value | RollingValue |
2013 | 1 | 111 | A | 1284 | 1284 | |
2013 | 1 | 111 | B | 572 | 572 | |
2013 | 1 | 222 | A | 536 | 536 | |
2013 | 1 | 222 | B | 822 | 822 | |
2013 | 2 | 111 | A | 1167 | 2451 | |
2013 | 2 | 111 | B | 1170 | 1742 | |
2013 | 2 | 222 | A | 780 | 1316 | |
2013 | 2 | 222 | B | 916 | 1738 | |
2013 | 3 | 111 | A | 1157 | 3608 | |
2013 | 3 | 111 | B | 1282 | 3024 | |
2013 | 3 | 222 | A | 1479 | 2795 | |
2013 | 3 | 222 | B | 1269 | 3007 | |
2013 | 4 | 111 | A | 868 | 4476 | |
2013 | 4 | 111 | B | 1003 | 4027 | |
2013 | 4 | 222 | A | 1004 | 3799 | |
2013 | 4 | 222 | B | 773 | 3780 | |
2013 | 5 | 111 | A | 1268 | 5744 | |
2013 | 5 | 111 | B | 669 | 4696 | |
2013 | 5 | 222 | A | 1255 | 5054 | |
2013 | 5 | 222 | B | 1222 | 5002 | |
2013 | 6 | 111 | A | 608 | 6352 | |
2013 | 6 | 111 | B | 1237 | 5933 | |
2013 | 6 | 222 | A | 723 | 5777 | |
2013 | 6 | 222 | B | 715 | 5717 |
here is a part of my data
hi Massimo,
I'm sorry but it doesn't work for some reason.
I'm attaching here an excel file with both the source table and the result.
I have filtered the data in order to show an example why the result is not as expected.
i don't what's the reason to get different result than you
It works!!
thanks
i needed to add another code as can be seen at the screen shot below
Thanks Massimo