Qlik Community

Qlik Sense App Development

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

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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 (4)
2 Solutions

Accepted Solutions
Highlighted
Master
Master

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

Highlighted
MVP
MVP

 

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
Highlighted
Master
Master

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") 😉
Highlighted
Contributor III
Contributor III

Thank you ! 

It works good 

Highlighted
Contributor III
Contributor III

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
Highlighted
Master
Master

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

Highlighted
Contributor III
Contributor III

Thank you very much 

Highlighted
MVP
MVP

 

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