Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
diptojyotidutta
Contributor II
Contributor II

Get Data for rest dates which are not there in dataset

Hi everyone,

I have 2 fields Date and Sales,

 

I have 3 rows. They are as stated under:-

 

Date,Sales

12/01/2012,45

14/01/2012,12

17/01/2012,47

 

Now their is no data of sales for 13, 15 and 16 of jan 2012. I want that the dates which are not there in the datasets show the previous value like 13/01/2012 will have sales value 45 because 12/01/2012 has it. 15/01/2012 and 16/01/2012 will have sales value 12 because 14/01/2012 has it. What must be the syntax?

 

1 Solution

Accepted Solutions
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Try this:

MC.PNG

Script:

Raw:
load * inline [
Date,Sales
12/01/2012,45
14/01/2012,12
17/01/2012,47
];

NoConcatenate
Data:
load Date#(Date,'DD/MM/YYYY')AS Date,Sales
resident Raw;

drop table Raw;

Date:
load min(Date) as minDate,
	 max(Date)as maxDate
resident Data;

let vMin =peek('minDate',0,'Date');
let vMax =peek('maxDate',0,'Date');
drop table Date;

concatenate(Data)
Data2:
load Date($(vMin)-1+IterNo(),'DD/MM/YYYY') AS Date,0 as Sales
AutoGenerate 1
while $(vMin)-1+IterNo()<$(vMax)+1;

NoConcatenate
DataSum:
load Date,Sum(Sales)as Sales
resident Data
group by Date
order by Date;

drop table Data;

DataLogic:
load Date,if(RowNo()=1,Sales,if(Sales=0,peek(SalesFull),Sales)) as SalesFull
resident DataSum;
drop table DataSum;

exit script;

 

Thanks and regards,

Arthur Fong 

View solution in original post

2 Replies
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Try this:

MC.PNG

Script:

Raw:
load * inline [
Date,Sales
12/01/2012,45
14/01/2012,12
17/01/2012,47
];

NoConcatenate
Data:
load Date#(Date,'DD/MM/YYYY')AS Date,Sales
resident Raw;

drop table Raw;

Date:
load min(Date) as minDate,
	 max(Date)as maxDate
resident Data;

let vMin =peek('minDate',0,'Date');
let vMax =peek('maxDate',0,'Date');
drop table Date;

concatenate(Data)
Data2:
load Date($(vMin)-1+IterNo(),'DD/MM/YYYY') AS Date,0 as Sales
AutoGenerate 1
while $(vMin)-1+IterNo()<$(vMax)+1;

NoConcatenate
DataSum:
load Date,Sum(Sales)as Sales
resident Data
group by Date
order by Date;

drop table Data;

DataLogic:
load Date,if(RowNo()=1,Sales,if(Sales=0,peek(SalesFull),Sales)) as SalesFull
resident DataSum;
drop table DataSum;

exit script;

 

Thanks and regards,

Arthur Fong 

diptojyotidutta
Contributor II
Contributor II
Author

Thanks it worked