topic Re: Repeat each row in a table until the enddate in QlikView

Repeat each row in a table until the enddate

Wed, 05 Feb 2014 16:04:13 GMT

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

Re: Repeat each row in a table until the enddate

Nicole-Smith — Wed, 05 Feb 2014 16:45:08 GMT

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.

Re: Repeat each row in a table until the enddate

Gysbert_Wassenaar — Wed, 05 Feb 2014 16:50:32 GMT

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.

Re: Repeat each row in a table until the enddate

MK_QSL — Wed, 05 Feb 2014 17:17:34 GMT

Nicole_Kowalsky and gwassenaar

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

Re: Repeat each row in a table until the enddate

Wed, 05 Feb 2014 17:40:38 GMT

Thanks it is working perfect

Re: Repeat each row in a table until the enddate

Nicole-Smith — Wed, 05 Feb 2014 17:45:29 GMT

I'm glad it's working for you!