Load scripts

[jduluc12]

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

[vchuprina]

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

[jduluc12]

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.

[jduluc12]

To make the rows unique row numbers can be introduced if that helps.

[vchuprina · Accepted solution]

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

[jduluc12]

Thanks a lot !