Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Looping

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

1 Solution

Accepted Solutions
Luminary
Luminary

Looping

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

3 Replies
Luminary
Luminary

Looping

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

MVP
MVP

Re: Looping

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

Not applicable

Looping

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

Community Browser