Looping

[#1]

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

[stephencredmond · Accepted solution]

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

[johnw]

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')
;

[#4]

Thanks guys, I manage to generate a similar table that I wanted, but still not 100%, I'll continue work on that. Appreciate it.