Re: Fill the missing dates - Qlik Community

Anonymous · ‎2020-01-15

Hello qlikers 🙂 ,

I have a table of suppliers.

Now I'm looking for the min date, here 11/2018 and for the max date, here 01/2020.

Now I want to loop from 11/2018 until 01/2020. The result should be so

So every supplier get all dates, but without values. But when they supplier have a date for example supplier 5 then he keep his values.

This is my code:

TempTable_Supplier:
NoConcatenate Load 
*
Resident Supplier;

// Find the min and the max date of all Productiondates 
MinMaxDate:
Load
Date(Min(Date#(ProductionDate, 'MM/YYYY')), 'MM/YYYY') as MinDate,
Date(Max(Date#(ProductionDate, 'MM/YYYY')), 'MM/YYYY') as MaxDate
Resident TempTable_Supplier;

Drop Table TempTable_Supplier;


Let varMinDate2 = Num(Peek('MinDate', 0, 'MinMaxDate'));  
Let varMaxDate2 = Num(Peek('MaxDate', 0, 'MinMaxDate')); 

MissingDate:
LOAD
$(varMinDate2) + Iterno()-1 As Num,  
Date($(varMinDate2) + IterNo() - 1) as TempDate  
AutoGenerate 1 While $(varMinDate2) + IterNo() -1 <= $(varMaxDate2);  

Calendar:  
Load  
  TempDate,  
  week(TempDate) As Week,  
  Year(TempDate) As Year,  
  Month(TempDate) As Month,  
  Day(TempDate) As Day,  
  ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,  
  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,  
  WeekDay(TempDate) as WeekDay  
Resident MissingDate
Order By TempDate ASC;  
Drop Table MissingDate;

First I find the min and max date. Now I want to loop, I copied the loop from my master calendar, but unfortunately it doesn't work.

Thank you in advance! Looking forward to reading some answers.

Arthur_Fong · ‎2020-01-15

Try this:

Script:

Raw:
load SupplierID,Date#(ProductionDate, 'MM/YYYY') as ProductionDate,Production,Delivery inline [
SupplierID,ProductionDate,Production,Delivery
5,11/2018,des,415
5,12/2019,as,15421
5,01/2020,des,14487
10,05/2019,des,51122
10,06/2019,des,14415
10,12/2019,as,
];
MinMaxDate:
Load
Min(Date#(ProductionDate, 'MM/YYYY')) as MinDate,
Max(Date#(ProductionDate, 'MM/YYYY')) as MaxDate
Resident Raw;

let vMin =peek('MinDate',0,'MinMaxDate');
let vMax =peek('MaxDate',0,'MinMaxDate');

Supplier:
load distinct
SupplierID,Production
resident Raw;

trace $(vMin);
trace $(vMax);
let vRow=NoOfRows('Supplier');

for i=0 to $(vRow)-1
let vSupplierID = peek('SupplierID',$(i),'Supplier');
let vProduction = peek('Production',$(i),'Supplier');
trace $(vSupplierID);

ZeroValue:
load
Date#(text(Date($(vMin)-1+IterNo(),'MM/YYYY')),'MM/YYYY') as ProductionDate,
'$(vSupplierID)' as SupplierID,
'$(vProduction)' as Production,
0 as Delivery
AutoGenerate 1	
while $(vMin)-1+IterNo()<$(vMax)+1;

next i

drop table Supplier;

NoConcatenate
Data:
load 
	ProductionDate,
	SupplierID,
	Production,
	sum(Delivery)as Delivery
resident Raw
group by 
	ProductionDate,
	SupplierID,
	Production;

drop table Raw;

exit SCRIPT;

Anonymous · ‎2020-01-15

Thank you for your answer.

Unfortunately the dates are double. If I have a date for example the 11/2018 then they shouldn't be a 11/2018 without a value. Only when the supplier hasn't delievered.

Arthur_Fong · ‎2020-01-15

Do you mean like this?

Script:

Raw:
load SupplierID,Date#(ProductionDate, 'MM/YYYY') as ProductionDate,Production,Delivery inline [
SupplierID,ProductionDate,Production,Delivery
5,11/2018,des,415
5,12/2019,as,15421
5,01/2020,des,14487
10,05/2019,des,51122
10,06/2019,des,14415
10,12/2019,as,
];
MinMaxDate:
Load
Min(Date#(ProductionDate, 'MM/YYYY')) as MinDate,
Max(Date#(ProductionDate, 'MM/YYYY')) as MaxDate
Resident Raw;

let vMin =peek('MinDate',0,'MinMaxDate');
let vMax =peek('MaxDate',0,'MinMaxDate');

Supplier:
load distinct
SupplierID,Production
resident Raw;

trace $(vMin);
trace $(vMax);
let vRow=NoOfRows('Supplier');

for i=0 to $(vRow)-1
let vSupplierID = peek('SupplierID',$(i),'Supplier');
let vProduction = peek('Production',$(i),'Supplier');
trace $(vSupplierID);


ZeroValue:
load
Date#(text(Date($(vMin)-1+IterNo(),'MM/YYYY')),'MM/YYYY') as ProductionDate,
'$(vSupplierID)' as SupplierID,
null() as Production,
0 as Delivery
AutoGenerate 1	
while $(vMin)-1+IterNo()<$(vMax)+1;

next i

drop table Supplier;

NoConcatenate
Data:
load 
	ProductionDate,
	SupplierID,
	Production,
	sum(Delivery)as Delivery
resident Raw
group by 
	ProductionDate,
	SupplierID,
	Production
order by ProductionDate,
		SupplierID;


NoConcatenate
Data2:
load * resident Data
WHERE
peek(ProductionDate)<>ProductionDate;
;

drop table Raw;
drop table Data;
exit SCRIPT;

Anonymous · ‎2020-01-15

Thank you agian! Great work. Yes, I mean this.

But

Supplier:
load distinct
SupplierID,Production
resident Raw;

and this

for i=0 to $(vRow)-1
let vSupplierID = peek('SupplierID',$(i),'Supplier');
let vProduction = peek('Production',$(i),'Supplier');
trace $(vSupplierID);

I have shorten my excel file. So I have more columns as the four. Is there another solution without call twice all the names?

Thank you in advance! 🙂

Arthur_Fong · ‎2020-01-15

Doesn't need the second variable (Production) anyway.

Just Load SupplierID will do.

Supplier:
load distinct
SupplierID
resident Raw;

For ZeroValue table:

ZeroValue:
load
Date#(text(Date($(vMin)-1+IterNo(),'MM/YYYY')),'MM/YYYY') as ProductionDate,
'$(vSupplierID)' as SupplierID,
null() as Production,
null() as Column3, //add in your third column
null() as Column4, //add in your 4th column
0 as Delivery
AutoGenerate 1	
while $(vMin)-1+IterNo()<$(vMax)+1;

Arthur_Fong · ‎2020-01-15

A better way for ZeroValue table is to force table concatenation to happen:

Concatenate(Raw)
ZeroValue:
load
Date#(text(Date($(vMin)-1+IterNo(),'MM/YYYY')),'MM/YYYY') as ProductionDate,
'$(vSupplierID)' as SupplierID,
0 as Delivery
AutoGenerate 1 
while $(vMin)-1+IterNo()<$(vMax)+1;

This way you doesn't need to assign the additional columns as nulls.

Anonymous · ‎2020-01-15

Thank you! 🙂

There is complication. When I'm using this script.

Raw:
load SupplierID,Date#(ProductionDate, 'MM/YYYY') as ProductionDate,Production,Delivery inline [
SupplierID,ProductionDate,Production,Delivery
5,11/2018,des,415
5,12/2019,as,15421
5,01/2020,des,14487
10,05/2019,des,51122
10,06/2019,des,14415
10,12/2019,as,
];
MinMaxDate:
Load
Min(Date#(ProductionDate, 'MM/YYYY')) as MinDate,
Max(Date#(ProductionDate, 'MM/YYYY')) as MaxDate
Resident Raw;

let vMin =peek('MinDate',0,'MinMaxDate');
let vMax =peek('MaxDate',0,'MinMaxDate');

Supplier:
load distinct
SupplierID,Production
resident Raw;

trace $(vMin);
trace $(vMax);
let vRow=NoOfRows('Supplier');

for i=0 to $(vRow)-1
let vSupplierID = peek('SupplierID',$(i),'Supplier');
let vProduction = peek('Production',$(i),'Supplier');
trace $(vSupplierID);


ZeroValue:
load
Date#(text(Date($(vMin)-1+IterNo(),'MM/YYYY')),'MM/YYYY') as ProductionDate,
'$(vSupplierID)' as SupplierID,
null() as Production,
0 as Delivery
AutoGenerate 1	
while $(vMin)-1+IterNo()<$(vMax)+1;

next i

drop table Supplier;

NoConcatenate
Data:
load 
	ProductionDate,
	SupplierID,
	Production,
	sum(Delivery)as Delivery
resident Raw
group by 
	ProductionDate,
	SupplierID,
	Production
order by ProductionDate,
		SupplierID;


NoConcatenate
Data2:
load * resident Data
WHERE
peek(ProductionDate)<>ProductionDate;
;

drop table Raw;
drop table Data;
exit SCRIPT;

Only the supplier with the ID 5 is shown, but all other suppliers should also be shown. 🙂

Thank you in advance!

Arthur_Fong · ‎2020-01-15

Try this:

Raw:
load SupplierID,Date#(ProductionDate, 'MM/YYYY') as ProductionDate,Production,Delivery inline [
SupplierID,ProductionDate,Production,Delivery
5,11/2018,des,415
5,12/2019,as,15421
5,01/2020,des,14487
10,05/2019,des,51122
10,06/2019,des,14415
10,12/2019,as,
];
MinMaxDate:
Load
Min(Date#(ProductionDate, 'MM/YYYY')) as MinDate,
Max(Date#(ProductionDate, 'MM/YYYY')) as MaxDate
Resident Raw;

let vMin =peek('MinDate',0,'MinMaxDate');
let vMax =peek('MaxDate',0,'MinMaxDate');

Supplier:
load distinct
SupplierID,Production
resident Raw;

trace $(vMin);
trace $(vMax);
let vRow=NoOfRows('Supplier');

for i=0 to $(vRow)-1
let vSupplierID = peek('SupplierID',$(i),'Supplier');
let vProduction = peek('Production',$(i),'Supplier');
trace $(vSupplierID);

Concatenate(Raw)
ZeroValue:
load
Date#(text(Date($(vMin)-1+IterNo(),'MM/YYYY')),'MM/YYYY') as ProductionDate,
'$(vSupplierID)' as SupplierID,
0 as Delivery
AutoGenerate 1	
while $(vMin)-1+IterNo()<$(vMax)+1;

next i



NoConcatenate
Data:
load 
	ProductionDate,
	SupplierID,
	Production,
	sum(Delivery)as Delivery
resident Raw
group by 
	ProductionDate,
	SupplierID,
	Production
order by
ProductionDate,
SupplierID;


//NoConcatenate
for i =0 to $(vRow)-1
let vSupplierID = peek('SupplierID',$(i),'Supplier');
Data2:
load *, 1 as Flag resident Data
WHERE
peek(ProductionDate)<>ProductionDate
and SupplierID='$(vSupplierID)'
//peek(SupplierID)=SupplierID;
;
next i
drop table Supplier;
drop table Raw;
drop table Data;
exit SCRIPT;

Fill the missing dates

excel

Loop