topic Re: Looping in QlikView

Looping

Fri, 10 Jun 2011 09:01:37 GMT

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

stephencredmond — Fri, 10 Jun 2011 22:29:46 GMT

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

johnw — Sat, 11 Jun 2011 00:26:42 GMT

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

Mon, 13 Jun 2011 08:50:05 GMT

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