topic Re: Last Two Month Names in Load Script in QlikView

Last Two Month Names in Load Script

tamilarasu — Tue, 29 Sep 2015 03:02:34 GMT

Hi All,

I want to load last two months data into my application. I just want to create a table which should stores the last two month names in below format. Say for example I have a variable called "vEndingMonth" and the month name stored as "Sep 2015". I want to create a table (by using the above variable) which should contains below month names.

Sep 2015

Aug 2015

July 2015

I'll use the above table when loading QVD's. I wish to use direct file names in my load script instead of looping through all QVD's.

Thank you in advance.

Re: Last Two Month Names in Load Script

SreeniJD — Tue, 29 Sep 2015 03:16:48 GMT

Hi Tamil Nagaraj,

There are multiple ways to achieve this. Let me suggest one way..you can create two columns like prev_month, prev_month-1 and with addmonths() function you can substract -1 for previous month and -2 for previous to previous month. with Date# function you can get the desired format..

HTH

Sreeni

Re: Last Two Month Names in Load Script

Digvijay_Singh — Tue, 29 Sep 2015 04:12:24 GMT

You can use below script -

Let vEndingMonth = Date(MakeDate(2015,09,01),'MMM YYYY');

Let vThreemonths = vEndingMonth &'_'& Date(AddMonths(vEndingMonth,-1),'MMM YYYY') &'_' &Date(AddMonths(vEndingMonth,-2),'MMM YYYY');

Month:

Load subField('$(vThreemonths)','_') as Date Autogenerate(1);

Re: Last Two Month Names in Load Script

tamilarasu — Tue, 29 Sep 2015 04:53:02 GMT

Hi Jd Sreeni,

Thanks for the suggestion and it's working fine. But, we need to fetch data from three columns. My expectation is all values in a single field and single table.

Hi DigVijay Singh,

I just modified the your code a bit. It's not taking previous months due to data format problem.

Let vThreemonths = '$(vEndingMonth)' &'_'& MonthName(Addmonths(Date#('$(vEndingMonth)','MMM YYYY'),-1)) &'_' &MonthName(Addmonths(Date#('$(vEndingMonth)','MMM YYYY'),-2));

Month:
Load subField('$(vThreemonths)','_') as Date Autogenerate(1);

Thanks to both of you. Have a nice day.

Re: Last Two Month Names in Load Script

tamilarasu — Tue, 29 Sep 2015 05:40:52 GMT

Just tried this and working like expected.

FOR i=-2 to 0 Step 1
MonthTemp:
LOAD MonthName(Addmonths(Date#('$(vEndingMonth)','MMM YYYY'),$(i))) as Month AutoGenerate(1);
NEXT