Repeat each row in a table until the enddate

[#1]

Hi ,

Here is the example , i am trying to do in the load script. Is there any easy way to do it in qlikview

Current table

KeyName	StartDate	EndDate
XYZ	201307	201309
BBB	201308	201309
CCC	201312	201402

Per name it has to repeat until the end date and start date has to change according

Desired Table:

KeyName	StartDate	EndDate
XYZ	201307	201309
XYZ	201308	201309
XYZ	201309	201309
BBB	201308	201309
BBB	201309	201309
CCC	201312	201402
CCC	201401	201402
CCC	201402	201402

Please Advise.

I am thinking of creating rowno() and looping it or iterno().

let me know if there are any easy ways

Thanks

rk

[Nicole-Smith · Accepted solution]

This should do the trick:

Temp:

LOAD KeyName, date#(StartDate, 'YYYYMM') as StartDate, date#(EndDate, 'YYYYMM') as EndDate INLINE [

    KeyName, StartDate, EndDate

    XYZ, 201307, 201309

    BBB, 201308, 201309

    CCC, 201312, 201402

];

Data:

NoConcatenate LOAD

    KeyName,

    date(AddMonths(StartDate,IterNo()-1), 'YYYYMM') as StartDate,

    EndDate

Resident Temp

while EndDate - addmonths(StartDate, IterNo()-1) >= 0;

DROP TABLE Temp;

I've also attached a working example.

[Gysbert_Wassenaar]

Something like this:

LOAD KeyName, date(addmonths(date#(StartDate,'YYYYMM'),IterNo()-1),'YYYYMM') as StartDate, EndDate
while addmonths(date#(StartDate,'YYYYMM'),IterNo()-1) <= date#(EndDate,'YYYYMM');
LOAD * INLINE [
    KeyName, StartDate, EndDate
    XYZ, 201307, 201309
    BBB, 201308, 201309
    CCC, 201312, 201402
];

The inline load is just an example to load your sample data. You can replace it with something else that loads data from a file or database.

[MK_QSL]

Nicole_Kowalsky and gwassenaar

You both have very good command on Scripting.... Always reading your answer specially on script...

[#5]

Thanks it is working perfect

[Nicole-Smith]

I'm glad it's working for you!