Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Sameer9585
Contributor II

Assigning values using Peek

Hi,

I have dataset which contains months and sales and some of the sales are Null.

I need that the null values to be assigned with the cumulative sum of previous values till that null value.

I provide the dataset below i that Sheet 2 is the sample.

 

Thanking You in advance,

1 Solution

Accepted Solutions
MVP
MVP

Re: Assigning values using Peek

This?

Load
	Month, Sales,
	If(Len(trim(Sales))=0,0,AccuSales) as AccuSales,
	If(Len(trim(Sales))=0, Peek(AccuSales), Sales) as NewSales
	;
Load *,
     RangeSum(Peek(AccuSales),Sales) as AccuSales	
	Inline [
Month	Sales
Jan	210
Feb	250
Mar	
Apr	150
May	265
Jun	
Jul	154
Aug	145
Sep	415
Oct	
Nov	156
Dec	
] (delimiter is spaces);
Drop Fields AccuSales;
7 Replies
MVP
MVP

Re: Assigning values using Peek

like this?

Load
	Month, Sales,AccuSales,
	If(Len(trim(Sales))=0, Peek(AccuSales), Sales) as NewSales
	;
Load *,
     RangeSum(Peek(AccuSales),Sales) as AccuSales	
	Inline [
Month	Sales
Jan	210
Feb	250
Mar	
Apr	150
May	265
Jun	
Jul	154
Aug	145
Sep	415
Oct	
Nov	156
Dec	
] (delimiter is spaces);

Drop Fields AccuSales;	
Sameer9585
Contributor II

Re: Assigning values using Peek

You are giving the rolling sum to previous also.

My scenario is in dataset if you observe I need the values of 

Mar-460

Jun-415

Oct-714

Dec-156

MVP
MVP

Re: Assigning values using Peek

This?

Load
	Month, Sales,
	If(Len(trim(Sales))=0,0,AccuSales) as AccuSales,
	If(Len(trim(Sales))=0, Peek(AccuSales), Sales) as NewSales
	;
Load *,
     RangeSum(Peek(AccuSales),Sales) as AccuSales	
	Inline [
Month	Sales
Jan	210
Feb	250
Mar	
Apr	150
May	265
Jun	
Jul	154
Aug	145
Sep	415
Oct	
Nov	156
Dec	
] (delimiter is spaces);
Drop Fields AccuSales;
Sameer9585
Contributor II

Re: Assigning values using Peek

Thanks a lot Smiley Happy

Sameer9585
Contributor II

Re: Assigning values using Peek

Can you explain me the logic ?

MVP
MVP

Re: Assigning values using Peek

Sameer, I am bad at explaining in writing. Smiley Tongue If you could let me know exactly which section of the code you need the clarification on, it would be easier for me to explain. 

Sameer9585
Contributor II

Re: Assigning values using Peek

Can we do the same with importing not by creating the Inline table?If yes, Then How?