3 Replies Latest reply: Jun 13, 2011 4:50 AM by Jason Vong RSS

    Looping

    Jason Vong

      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

        • Looping
          Stephen Redmond

          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

            • Re: Looping
              John Witherspoon

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

            • Looping
              Jason Vong

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