topic Help to created an expanded table with dates in App Development

Help to created an expanded table with dates

Xabinav — Sat, 16 Nov 2024 01:52:50 GMT

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

Re: Help to created an expanded table with dates

Taoufiq_Zarra — Thu, 16 Jul 2020 14:42:02 GMT

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:

Re: Help to created an expanded table with dates

Xabinav — Thu, 16 Jul 2020 15:24:17 GMT

Thank you !

It works good

Re: Help to created an expanded table with dates

Xabinav — Thu, 16 Jul 2020 16:14:46 GMT

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

Re: Help to created an expanded table with dates

Taoufiq_Zarra — Thu, 16 Jul 2020 21:05:44 GMT

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 :

Re: Help to created an expanded table with dates

Xabinav — Thu, 16 Jul 2020 22:29:22 GMT

Thank you very much

Re: Help to created an expanded table with dates

Kushal_Chawda — Sat, 18 Jul 2020 12:40:25 GMT

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;