Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
The data looks like this
txn | item | amount |
1 | item1 | 34 |
1 | item2 | 35 |
1 | Special | |
1 | item1 | 60 |
1 | Item3 | 90 |
1 | Special | |
2 | Item3 | 80 |
2 | Item4 | 90 |
2 | Special |
We need a load script that can produce output as shown below.
txn | item | amount | New Column |
1 | item1 | 34 | |
1 | item2 | 35 | |
1 | Special | 34+35 = 69 | |
1 | item1 | 60 | |
1 | Item3 | 90 | |
1 | Special | 60+90 = 150 | |
2 | Item3 | 80 | |
2 | Item4 | 90 | |
2 | Special | 80+90 = 170 |
Wherever there is a Special item, it should go up and sum the amount of other items until it reaches another Special Item within the same Txn.
Thanks,
Jean
Hi,
Please try the following script:
Data_TMP:
LOAD
IF(item = 'Special', RowNo(), null()) AS SpecialUniqueID,
IF(Len(amount)= 0, null(), amount) As amount,
RowNo() as UniqueID,
txn,
item;
LOAD * Inline [
txn, item, amount
1, item1, 34
1, item2, 35
1, Special,
1, item1, 60
1, Item3, 90
1, Special,
2, Item3, 80
2, Item4, 90
2, Special,
];
Data_TMP2:
NoConcatenate
LOAD
IF([item] <> 'Special', Peek(SpecialUniqueID), SpecialUniqueID) AS SpecialUniqueID,
txn,
item,
amount,
UniqueID
Resident Data_TMP
Order By UniqueID desc;
DROP Table Data_TMP;
Left Join(Data_TMP2)
LOAD
'Special' as item,
SpecialUniqueID,
sum(amount) AS amount2
Resident Data_TMP2
Group By SpecialUniqueID;
Data:
NoConcatenate
LOAD
SpecialUniqueID,
txn,
item,
UniqueID,
IF(isnull(amount), amount2, amount) AS amount
Resident Data_TMP2;
DROP Table Data_TMP2;
My result:
Regards,
Vitalii
Hi Jean,
How do you sort rows in your table?
For Instance, txn is 1 for all these rows, how do you know that item1 and item2 or item1 and item3 should be summed?
txn | item | amount |
1 | item1 | 34 |
1 | item2 | 35 |
1 | Special | |
1 | item1 | 60 |
1 | Item3 | 90 |
1 | Special |
Whatever items appear before Special in the same Txn should be summed up and the result of that sum will appear for Special.
It is like doing a 'Group by' by Special and Txn.
The first special should have the sum of 34 and 35
And
The second Special should have the sum of 60 and 90.
To make the rows unique row numbers can be introduced if that helps.
Hi,
Please try the following script:
Data_TMP:
LOAD
IF(item = 'Special', RowNo(), null()) AS SpecialUniqueID,
IF(Len(amount)= 0, null(), amount) As amount,
RowNo() as UniqueID,
txn,
item;
LOAD * Inline [
txn, item, amount
1, item1, 34
1, item2, 35
1, Special,
1, item1, 60
1, Item3, 90
1, Special,
2, Item3, 80
2, Item4, 90
2, Special,
];
Data_TMP2:
NoConcatenate
LOAD
IF([item] <> 'Special', Peek(SpecialUniqueID), SpecialUniqueID) AS SpecialUniqueID,
txn,
item,
amount,
UniqueID
Resident Data_TMP
Order By UniqueID desc;
DROP Table Data_TMP;
Left Join(Data_TMP2)
LOAD
'Special' as item,
SpecialUniqueID,
sum(amount) AS amount2
Resident Data_TMP2
Group By SpecialUniqueID;
Data:
NoConcatenate
LOAD
SpecialUniqueID,
txn,
item,
UniqueID,
IF(isnull(amount), amount2, amount) AS amount
Resident Data_TMP2;
DROP Table Data_TMP2;
My result:
Regards,
Vitalii
Thanks a lot !