Sum of current and previous rows in load script

[jmialoundama]

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

[RsQK · Accepted solution]

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;

[jmialoundama]

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