topic Re: Load scripts in App Development

Load scripts

jduluc12 — Fri, 15 Apr 2022 07:20:00 GMT

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

Re: Load scripts

vchuprina — Fri, 15 Apr 2022 10:42:27 GMT

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

Re: Load scripts

jduluc12 — Fri, 15 Apr 2022 14:15:04 GMT

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.

Re: Load scripts

jduluc12 — Fri, 15 Apr 2022 14:24:14 GMT

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

Re: Load scripts

vchuprina — Sat, 16 Apr 2022 06:26:15 GMT

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

Re: Load scripts

jduluc12 — Sun, 17 Apr 2022 17:38:16 GMT

Thanks a lot !