topic transform table (range of dates) in QlikView

transform table (range of dates)

Thu, 17 May 2012 09:00:39 GMT

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

table1:

ActionName	DateStart	DateEnd
Акция 1	01.01.2012	05.01.2012
Акция 2	01.01.2012	07.01.2012
Акция 3	30.12.2011	02.01.2012

Result table:

ActionName	DateStart	DateEnd	Date
Акция 1	01.01.2012	05.01.2012	01.01.2012
Акция 1	01.01.2012	05.01.2012	02.01.2012
Акция 1	01.01.2012	05.01.2012	03.01.2012
Акция 1	01.01.2012	05.01.2012	04.01.2012
Акция 1	01.01.2012	05.01.2012	05.01.2012
Акция 2	01.01.2012	07.01.2012	01.01.2012
Акция 2	01.01.2012	07.01.2012	02.01.2012
Акция 2	01.01.2012	07.01.2012	03.01.2012
Акция 2	01.01.2012	07.01.2012	04.01.2012
Акция 2	01.01.2012	07.01.2012	05.01.2012
Акция 2	01.01.2012	07.01.2012	06.01.2012
Акция 2	01.01.2012	07.01.2012	07.01.2012
Акция 3	30.12.2011	02.01.2012	30.12.2011
Акция 3	30.12.2011	02.01.2012	31.12.2011
Акция 3	30.12.2011	02.01.2012	01.01.2012
Акция 3	30.12.2011	02.01.2012	02.01.2012

Re: transform table (range of dates)

Miguel_Angel_Baeyens — Thu, 17 May 2012 09:22:09 GMT

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

Re: transform table (range of dates)

Sokkorn — Thu, 17 May 2012 09:33:12 GMT

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

Re: transform table (range of dates)

Thu, 17 May 2012 09:37:38 GMT

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

Re: transform table (range of dates)

Tue, 22 May 2012 06:07:09 GMT

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.