Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

4 Replies
Miguel_Angel_Baeyens

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
Master
Master

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
Author

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
Author

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.