Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
adimiz123
Contributor III
Contributor III

Help to created an expanded table with dates

Hi everyone, I need your help to create the second Table from this... 

duplicate lines between two records  by date. 

1.  source table 

CarNoDate LINE 
12315/07/20205
12312/07/20204
12310/07/20203
12309/07/20202
12306/07/20201

 

2. Destination form 

CarNoDate LINE 
12315/07/20205
12314/07/20205
12313/07/20205
12312/07/20204
12311/07/20204
12310/07/20203
12309/07/20202
12308/07/20202
12307/07/20202
12306/07/20201

 

Thank you , Adiel 

Labels (2)
2 Solutions

Accepted Solutions
Taoufiq_Zarra

One solution :

Data:

LOAD CarNo, Date(Date) as Date , LINE INLINE [
    CarNo, Date , LINE 
    123, 15/07/2020, 5
    123, 12/07/2020, 4
    123, 10/07/2020, 3
    123, 09/07/2020, 2
    123, 06/07/2020, 1
    555, 11/07/2020, 2
    555, 09/07/2020, 1
];

Dataoutput:

load distinct CarNo resident Data;

Final:

LOAD 1 as Tmp autogenerate 1;

For vRow = 1 to NoOfRows('Dataoutput')

Let MyVar = Peek('CarNo',vRow-1,'Dataoutput');


	Tmp:
	noconcatenate
	
	load * resident Data where CarNo='$(MyVar)' order by Date ;
	
	Let vMin = Floor(Peek('Date',0));
	
	Let vMax = Floor(Peek('Date',-1));
	
	 
	Join LOAD Date($(vMin))+recno()-1 as Date
	
	AutoGenerate $(vMax)-$(vMin)+1;
	
		
	outputTmp:
	noconcatenate
	
	load * resident Tmp order by Date DESC;
	
	drop table Tmp;
	
    Join(Final)
	load if(IsNull(CarNo),peek(CarNo),CarNo) as CarNo,Date ,if(IsNull(LINE),peek(LINE),LINE) as LINE resident outputTmp;
	

	drop table outputTmp;
	
Next
drop table Data,Dataoutput;
DROP Field Tmp;

 

output :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Kushal_Chawda

 

You should always avoid for loops with complex transformation on your data set which could cause performance impact. Try this approach

 

Data:
LOAD * INLINE [
    CarNo, Date , LINE 
    123, 15/07/2020, 5
    123, 12/07/2020, 4
    123, 10/07/2020, 3
    123, 09/07/2020, 2
    123, 06/07/2020, 1
    555, 11/07/2020, 2
    555, 09/07/2020, 1
];

T1:
Load CarNo,
      Date,
      LINE,
     If(CarNo=Peek(CarNo),Peek(Date)+1,Date) as MinDate
Resident Data
Order by CarNo,Date;

Drop Table Data;

Final:
NoConcatenate
Load CarNo,
     LINE,
     date(MinDate+IterNo()-1) as Date
Resident T1
While MinDate+IterNo()-1<= Date;

Drop Table T1;

Drop Field MinDate;

 

View solution in original post

6 Replies
Taoufiq_Zarra

Hi,

One solution

I've left the long code to explain the approach :

Data:

LOAD CarNo, Date(Date) as Date , LINE INLINE [
    CarNo, Date , LINE 
    123, 15/07/2020, 5
    123, 12/07/2020, 4
    123, 10/07/2020, 3
    123, 09/07/2020, 2
    123, 06/07/2020, 1
];

Tmp:
noconcatenate
load * resident Data order by Date;

Let vMin = Floor(Peek('Date',0));

Let vMax = Floor(Peek('Date',-1));

 
Join LOAD Date($(vMin))+recno()-1 as Date

AutoGenerate $(vMax)-$(vMin)+1;

drop table Data;

outputTmp:
noconcatenate

load * resident Tmp order by Date DESC;

drop table Tmp;

output:
noconcatenate
load if(IsNull(CarNo),peek(CarNo),CarNo) as CarNo,Date,if(IsNull(LINE),peek(LINE),LINE) as LINE resident outputTmp;

drop table outputTmp;

 

 

output:

 

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
adimiz123
Contributor III
Contributor III
Author

Thank you ! 

It works good 

adimiz123
Contributor III
Contributor III
Author

Hi,  

Regarding the same issue, when another "CarNo" is added to the table, the code needs to be arranged,

I would be happy for your help please.

for example...

1. source - 

CarNoDate LINE 
12315/07/20205
12312/07/20204
12310/07/20203
12309/07/20202
12306/07/20201
55511/07/20202
55509/07/20201

 

2. destination 

CarNoDate LINE 
12315/07/20205
12314/07/20205
12313/07/20205
12312/07/20204
12311/07/20204
12310/07/20203
12309/07/20202
12308/07/20202
12307/07/20202
12306/07/20201
55511/07/20202
55510/07/20202
55509/07/20201
Taoufiq_Zarra

One solution :

Data:

LOAD CarNo, Date(Date) as Date , LINE INLINE [
    CarNo, Date , LINE 
    123, 15/07/2020, 5
    123, 12/07/2020, 4
    123, 10/07/2020, 3
    123, 09/07/2020, 2
    123, 06/07/2020, 1
    555, 11/07/2020, 2
    555, 09/07/2020, 1
];

Dataoutput:

load distinct CarNo resident Data;

Final:

LOAD 1 as Tmp autogenerate 1;

For vRow = 1 to NoOfRows('Dataoutput')

Let MyVar = Peek('CarNo',vRow-1,'Dataoutput');


	Tmp:
	noconcatenate
	
	load * resident Data where CarNo='$(MyVar)' order by Date ;
	
	Let vMin = Floor(Peek('Date',0));
	
	Let vMax = Floor(Peek('Date',-1));
	
	 
	Join LOAD Date($(vMin))+recno()-1 as Date
	
	AutoGenerate $(vMax)-$(vMin)+1;
	
		
	outputTmp:
	noconcatenate
	
	load * resident Tmp order by Date DESC;
	
	drop table Tmp;
	
    Join(Final)
	load if(IsNull(CarNo),peek(CarNo),CarNo) as CarNo,Date ,if(IsNull(LINE),peek(LINE),LINE) as LINE resident outputTmp;
	

	drop table outputTmp;
	
Next
drop table Data,Dataoutput;
DROP Field Tmp;

 

output :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
adimiz123
Contributor III
Contributor III
Author

Thank you very much 

Kushal_Chawda

 

You should always avoid for loops with complex transformation on your data set which could cause performance impact. Try this approach

 

Data:
LOAD * INLINE [
    CarNo, Date , LINE 
    123, 15/07/2020, 5
    123, 12/07/2020, 4
    123, 10/07/2020, 3
    123, 09/07/2020, 2
    123, 06/07/2020, 1
    555, 11/07/2020, 2
    555, 09/07/2020, 1
];

T1:
Load CarNo,
      Date,
      LINE,
     If(CarNo=Peek(CarNo),Peek(Date)+1,Date) as MinDate
Resident Data
Order by CarNo,Date;

Drop Table Data;

Final:
NoConcatenate
Load CarNo,
     LINE,
     date(MinDate+IterNo()-1) as Date
Resident T1
While MinDate+IterNo()-1<= Date;

Drop Table T1;

Drop Field MinDate;