Help to created an expanded table with dates

[Xabinav]

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

duplicate lines between two records  by date. 

1.  source table 

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

2. Destination form 

CarNo	Date	LINE
123	15/07/2020	5
123	14/07/2020	5
123	13/07/2020	5
123	12/07/2020	4
123	11/07/2020	4
123	10/07/2020	3
123	09/07/2020	2
123	08/07/2020	2
123	07/07/2020	2
123	06/07/2020	1

Thank you , Adiel 

[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:

[Xabinav]

Thank you ! 

It works good 

[Xabinav]

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 - 

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

2. destination 

CarNo	Date	LINE
123	15/07/2020	5
123	14/07/2020	5
123	13/07/2020	5
123	12/07/2020	4
123	11/07/2020	4
123	10/07/2020	3
123	09/07/2020	2
123	08/07/2020	2
123	07/07/2020	2
123	06/07/2020	1
555	11/07/2020	2
555	10/07/2020	2
555	09/07/2020	1

[Taoufiq_Zarra · Accepted solution]

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 :

[Xabinav]

Thank you very much 

[Kushal_Chawda · Accepted solution]

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;