topic Month do...loop in QlikView

Month do...loop

Fri, 06 Nov 2009 18:56:27 GMT

Hi, New user here! I have a table which lists:

Customer, Contract value (year), start-up date of contract.

I want a script so that I can create a new table to split from annual contract value to contract value per month. (It needs to be script so that I can use the table for further analysis)

EG.

Current Table:

Customer A, $120000, 1Jan2010.

Customer B, $480, 1Mar2010.

Desired Table:

Customer A, $10000, 1Jan2010

CustomerA, $10000, 1Feb2010

CustomerA, $10000, 1Mar2010

...

Customer B, $40, 1Mar2010

Customer B, $40, 1Apr2010

etc.

Customers have different contract start up dates

I think it should be possible through a do...loop, but can't seem to get the code correct. Any ideas?

Month do...loop

vidyut — Fri, 06 Nov 2009 20:00:46 GMT

You can do it very easily without using a loop.

Create a table with Every month of the year (Could be more if you want ).

Left Join to the current table 9so every Customer has 12 rows or more (Cartesian Join ).

Divide the Value by 12 to get the monthly value.

Try on similar lines. You do not need a for loop in any case.

Month do...loop

Fri, 06 Nov 2009 20:26:41 GMT

Thankyou for your reply. However I only want the monthly value to show for 12 months.

For example Customer A has a 12 month contract from Jan2010. Therefore they will have a monthly value from Jan2010-Dec2010, but not for January 2011 and later dates.

Customer B will have a monthly value from Mar2010-Feb2011, but not Jan2010, Feb 2010 or Apr2011.

Because customers have different contract start dates I can not see how a simple left join will work, since I then need to specify which 12 months need to have the monthly value and which ones should be blank/0.

I think your example does not take account of the start date?

Month do...loop

vidyut — Fri, 06 Nov 2009 20:36:36 GMT

🙂 it does take care of that. Instead of creating a Calendar, you just generate a number say OFFSET=1..12 and do the join. Create a new field NewMonth=OFFSET+CurrentMonth, and that should do.

Month do...loop

Fri, 06 Nov 2009 21:03:52 GMT

Thanks, you are right. I have set up an offset column and it works perfectly now.