Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

transform table (range of dates)

Hi. I need to transform the table1 to result table (in a loading script). How can i do that? Thanks in advance!

table1:

ActionName

DateStartDateEnd
Акция 101.01.201205.01.2012
Акция 201.01.201207.01.2012
Акция 330.12.201102.01.2012

Result table:

ActionNameDateStartDateEndDate
Акция 101.01.201205.01.201201.01.2012
Акция 101.01.201205.01.201202.01.2012
Акция 101.01.201205.01.201203.01.2012
Акция 101.01.201205.01.201204.01.2012
Акция 101.01.201205.01.201205.01.2012
Акция 201.01.201207.01.201201.01.2012
Акция 201.01.201207.01.201202.01.2012
Акция 201.01.201207.01.201203.01.2012
Акция 201.01.201207.01.201204.01.2012
Акция 201.01.201207.01.201205.01.2012
Акция 201.01.201207.01.201206.01.2012
Акция 201.01.201207.01.201207.01.2012
Акция 330.12.201102.01.201230.12.2011
Акция 330.12.201102.01.201231.12.2011
Акция 330.12.201102.01.201201.01.2012
Акция 330.12.201102.01.201202.01.2012
Tags (3)
1 Solution

Accepted Solutions

Re: transform table (range of dates)

Hi Sergey,


Take a look at the following script. I'm using two variables vMinDate and vMaxDate to create a dummy table labelled "Calendar" that has all possible dates between the lowest and highest date of the Data table. Then using the IntervalMatch() load and JOIN, the table is completed with each range discrete date.

Data:

LOAD * INLINE [

ActionName, DateStart, DateEnd

Акция 1, 01/01/2012, 05/01/2012

Акция 2, 01/01/2012, 07/01/2012

Акция 3, 30/12/2011, 02/01/2012

];

LET vMinDate = Num(Date('30/12/2011'));

LET vMaxDate = Num(Date('07/01/2012'));

Calendar:

LOAD Date($(vMinDate) + RecNo() -1) AS Date

AUTOGENERATE $(vMaxDate) - $(vMinDate) +1;

FullTable:

JOIN (Data) INTERVALMATCH (Date) LOAD DateStart, DateEnd

RESIDENT Data;

DROP TABLE Calendar;

Hope that helps.

Miguel

4 Replies

Re: transform table (range of dates)

Hi Sergey,


Take a look at the following script. I'm using two variables vMinDate and vMaxDate to create a dummy table labelled "Calendar" that has all possible dates between the lowest and highest date of the Data table. Then using the IntervalMatch() load and JOIN, the table is completed with each range discrete date.

Data:

LOAD * INLINE [

ActionName, DateStart, DateEnd

Акция 1, 01/01/2012, 05/01/2012

Акция 2, 01/01/2012, 07/01/2012

Акция 3, 30/12/2011, 02/01/2012

];

LET vMinDate = Num(Date('30/12/2011'));

LET vMaxDate = Num(Date('07/01/2012'));

Calendar:

LOAD Date($(vMinDate) + RecNo() -1) AS Date

AUTOGENERATE $(vMaxDate) - $(vMinDate) +1;

FullTable:

JOIN (Data) INTERVALMATCH (Date) LOAD DateStart, DateEnd

RESIDENT Data;

DROP TABLE Calendar;

Hope that helps.

Miguel

Sokkorn
Honored Contributor

Re: transform table (range of dates)

Hi,

Let try

[Data]:

LOAD RecNo()    AS [No],*;

LOAD * INLINE [

ActionName,    DateStart,    DateEnd

Акция 1,    01-01-2012,    05-01-2012

Акция 2,    01-01-2012,    07-01-2012

Акция 3,    30-12-2011,    02-01-2012];

SET vRecCount = NoOfRows('Data')-1;

FOR i = 0 TO $(vRecCount)

    vMinDate = NUM(PEEK('DateStart',$(i),'Data'));

    vMaxDate = NUM(PEEK('DateEnd',$(i),'Data'));

    vNo = PEEK('No',$(i),'Data');

    IF i = 0 THEN

        recCount = 0;

    ELSE

        recCount = NoOfRows('DataCalendar');

    ENDIF

    DataCalendar:

    LOAD $(vNo) AS No,     

      Date($(vMinDate) + RowNo()-$(recCount)-1) AS Dates

    AUTOGENERATE 1

    WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

NEXT

NoConcatenate

[Table1]:

LOAD * RESIDENT Data;

RIGHT JOIN

LOAD * RESIDENT DataCalendar;

DROP TABLE DataCalendar,Data;

See sample attached file.

Regards,

Sokkorn

Not applicable

Re: transform table (range of dates)

Hi,

Try this code may be it can help you.

DATA:
LOAD * INLINE [
    ActionName, Date Start, Date End
    ABC, 01/01/2012, 05/01/2012
    PQR, 03/01/2012, 07/01/2012
    XYZ, 05/01/2012, 08/01/2012
];


TEST:
LOAD * INLINE [
    DATES
    01/01/2012
    02/01/2012
    03/01/2012
    04/01/2012
    05/01/2012
    06/01/2012
    07/01/2012
    08/01/2012
    09/01/2012
    10/01/2012
];

inner join
IntervalMatch ( DATES ) LOAD [Date Start],[Date End] RESIDENT DATA;

inner join
LOAD *
RESIDENT DATA;

DROP TABLE DATA;

Thanks & Best Regards,

Kuldeep Tak

Not applicable

Re: transform table (range of dates)

thank you very much, guys! all answers is correct,

1 method  good for a small amount of lines (need a lot of memory)

2 method dont need much memory but takes a lot of time.