Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
For example I have a table with attribute [ID; AMOUNT; VALID_FROM; VALID_TO],
and having these 3 records:
ACT123 100 10/6/2011 11/6/2011
ACT123 150 11/6/2011 15/6/2011
ACT123 80 15/6/2011 1/1/5999
and I want to loop the table and create a new table with attribute [ID; AMOUNT; TXN_DATE],
and having output as follows:
ACT123 100 10/6/2011
ACT123 150 11/6/2011
ACT123 150 12/6/2011
ACT123 150 13/6/2011
ACT123 150 14/6/2011
ACT123 80 15/6/2011
help me out please,
Thanks in advance,
Jason
Hi Jason,
You probably want to investigate IntervalMatch for this:
Data:
LOAD * INLINE [
ID,AMOUNT,VALID_FROM,VALID_TO
ACT123, 100, 10/6/2011, 11/6/2011
ACT123, 150, 11/6/2011, 15/6/2011
ACT123, 80, 15/6/2011, 1/1/5999
];
Let vMinDate=Floor(MakeDate(2011,6,10));
Let vMaxDate=Floor(MakeDate(2011,6,15));
Calendar:
Load
TempDate As DateKey,
Date(TempDate) AS DisplayDate,
Year(TempDate) As Year,
Month(TempDate) As Month,
Date(TempDate) As Day;
Load
RecNo()-1+$(vMinDate) As TempDate
AutoGenerate($(vMaxDate)-$(vMinDate)+1);
DateLink:
IntervalMatch(DateKey)
Load
VALID_FROM, VALID_TO
Resident
Data;
Regards,
Stephen
Hi Jason,
You probably want to investigate IntervalMatch for this:
Data:
LOAD * INLINE [
ID,AMOUNT,VALID_FROM,VALID_TO
ACT123, 100, 10/6/2011, 11/6/2011
ACT123, 150, 11/6/2011, 15/6/2011
ACT123, 80, 15/6/2011, 1/1/5999
];
Let vMinDate=Floor(MakeDate(2011,6,10));
Let vMaxDate=Floor(MakeDate(2011,6,15));
Calendar:
Load
TempDate As DateKey,
Date(TempDate) AS DisplayDate,
Year(TempDate) As Year,
Month(TempDate) As Month,
Date(TempDate) As Day;
Load
RecNo()-1+$(vMinDate) As TempDate
AutoGenerate($(vMaxDate)-$(vMinDate)+1);
DateLink:
IntervalMatch(DateKey)
Load
VALID_FROM, VALID_TO
Resident
Data;
Regards,
Stephen
It can also be done with a while loop. (Edit: I'm not saying this is better than intervalmatch for this case, just that it's an alternative that may be better for SOME cases, so I think is worth mentioning.)
NewTable:
LOAD
ID
,AMOUNT
,date(VALID_FROM + 1 - iterno()) as TXN_DATE
RESIDENT Data
WHILE VALID_FROM + 1 - iterno() < VALID_TO
;
I'm not sure why you'd put AMOUNT on this table, though. And I would think you'd want to include VALID_TO as a TXN_DATE, but you don't appear to. If you meant to include it, just use <= instead of <. Also, I was unclear what was going on with your 1/1/5999 date. I'd normally think that means "valid forever", where you instead treated it as "only valid for the VALID_FROM date". My solution above treats it as valid forever. If you don't need future dates, make sure to put in today() as a limit as well, like by doing rangemin(today(),VALID_TO).
And if we want a calendar, we can now build it from TXN_DATE:
Calendar:
LOAD *
,year(TXN_DATE) as TXN_YEAR
...etc...
;
LOAD date(fieldvalue('TXN_DATE',recno())) as TXN_DATE
AUTOGENERATE fieldvaluecount('TXN_DATE')
;
Thanks guys, I manage to generate a similar table that I wanted, but still not 100%, I'll continue work on that. Appreciate it.