Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
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
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
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.