Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
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

 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

Labels (2)

• ### Interval Dates

2 Solutions

Accepted Solutions

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:

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));

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 :

Regards,
Taoufiq ZARRA

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

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

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

``````Data:
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:
Date,
LINE,
If(CarNo=Peek(CarNo),Peek(Date)+1,Date) as MinDate
Resident Data
Order by CarNo,Date;

Drop Table Data;

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

Drop Table T1;

Drop Field MinDate;``````

6 Replies

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));

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:

Regards,
Taoufiq ZARRA

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

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

Thank you !

It works good

Contributor III
Author

Hi,

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

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

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:

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));

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 :

Regards,
Taoufiq ZARRA

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

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

Thank you very much

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

``````Data:
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:
Date,
LINE,
If(CarNo=Peek(CarNo),Peek(Date)+1,Date) as MinDate
Resident Data
Order by CarNo,Date;

Drop Table Data;

Final:
NoConcatenate