topic Re: Sum of current and previous rows in load script in App Development

Sum of current and previous rows in load script

jmialoundama — Tue, 25 Oct 2022 08:55:38 GMT

Hi,

I have this script and this table output:

Donnees_Inline: LOAD * INLINE [ sortie_stock_pn_ic,sortie_stock_pn_article,sortie_stock_date,sortie_stock_quantite,sortie_stock_reference,sortie_stock_type_mouvement 008Q614,61037916AA,30/09/2022,1,,VP 008Q614,61037916AA,02/02/2023,1,,VP 008Q614,61037916AA,28/02/2023,,3,Peremption 008Q614,61037916AA,30/05/2023,1,,VP 008Q614,61037916AA,02/10/2023,1,,VP 008Q614,61037916AA,31/12/2023,,2,Peremption 008Q614,61037916AA,30/01/2024,1,,VP 008Q614,61037916AA,31/05/2024,,5,Peremption 008Q614,61037916AA,02/06/2024,1,,VP 008Q614,61037916AA,30/09/2024,1,,VP 008Q614,61037916AA,31/12/2024,,1,Peremption 1234,1234BB,05/10/2022,1,,VP 1234,1234BB,05/05/2023,1,,VP 1234,1234BB,06/07/2023,,5,Peremption 1234,1234BB,01/10/2024,1,,VP 1234,1234BB,11/11/2024,,1,Peremption 1234,1234BB,03/12/2024,1,,VP 1234,1234BB,12/12/2024,,3,Peremption ]; NoConcatenate Donnees: LOAD sortie_stock_pn_ic & sortie_stock_pn_article as key_ic_article , sortie_stock_pn_ic ,sortie_stock_pn_article ,date(sortie_stock_date) as sortie_stock_date ,sortie_stock_quantite ,sortie_stock_reference //,sortie_stock_type_mouvement Resident Donnees_Inline ORDER BY sortie_stock_date,sortie_stock_pn_ic,sortie_stock_pn_article; DROP TABLE Donnees_Inline; NoConcatenate Peremptions: LOAD distinct key_ic_article, sortie_stock_pn_ic, sortie_stock_pn_article, sortie_stock_date, //sortie_stock_quantite, sortie_stock_reference, If(Previous(sortie_stock_pn_ic)=sortie_stock_pn_ic AND sortie_stock_reference <>'' ,RANGESUM(sortie_stock_reference,Peek('sortie_stock_reference'))) As Stock_reference_sum //sortie_stock_type_mouvement RESIDENT Donnees //where sortie_stock_reference <>'' ORDER BY sortie_stock_date,sortie_stock_pn_ic,sortie_stock_pn_article;

I want this in final :

In my load script i have add this line, but the result is not good :

If(Previous(sortie_stock_pn_ic)=sortie_stock_pn_ic,RangeSum(sortie_stock_reference,Peek('Stock_reference_sum')),sortie_stock_reference) As Stock_reference_cum

Thanks in advance for your help

Re: Sum of current and previous rows in load script

RsQK — Tue, 25 Oct 2022 08:56:04 GMT

Hi, try this script:

temp_data: LOAD * INLINE [ sortie_stock_pn_ic,sortie_stock_date,sortie_stock_reference 1234,05.10.2022, 1234,05.05.2023, 1234,06.07.2023,5 1234,01.10.2024, 1234,11.11.2024,1 1234,03.12.2024, 1234,12.12.2024,3 ]; temp_data2: LOAD *, sortie_stock_reference AS sortie_stock_reference_cumulative RESIDENT temp_data ORDER BY sortie_stock_pn_ic,sortie_stock_date; DROP TABLE temp_data; temp_data3: NOCONCATENATE LOAD sortie_stock_pn_ic, sortie_stock_date, sortie_stock_reference, IF(PREVIOUS(sortie_stock_pn_ic)=sortie_stock_pn_ic,RANGESUM(PEEK(sortie_stock_reference_cumulative),sortie_stock_reference_cumulative,0),RANGESUM(sortie_stock_reference_cumulative,0)) AS sortie_stock_reference_cumulative RESIDENT temp_data2; DROP TABLE temp_data2;

Re: Sum of current and previous rows in load script

jmialoundama — Tue, 25 Oct 2022 09:37:48 GMT

Hi @RsQK ,

Thanks so much for your help.

I test with data :

LOAD * INLINE [
sortie_stock_pn_ic,sortie_stock_date,sortie_stock_reference
1234,05/10/2022
1234,05/05/2023
1234,06/07/2023,5
1234,01/10/2024
1234,11/11/2024
1234,03/12/2024,1
1234,12/12/2024,3
];

And i have a bad result :

In the date of 01/10/2024 instead of have 0, i have 5. Is the same for date 11/11/2024, i have 5 instead of 0