topic Need to replace "fixed" variable name with a dynamic one, based on date. in QlikView

Need to replace "fixed" variable name with a dynamic one, based on date.

Mon, 23 Jun 2014 22:36:27 GMT

I am importing an excel file which has three fields called "Quota_M1", "Quota_M2" and "Quota_M3". The 1/2/3 in these fields represent the month number in the quarter. I want my app to reconize the month of the quarter at the time of LOAD and have an expression which SUMS the corresponding "Quota" value.

For example, if I load the data today using the code below, I want my expression to be Sum(Quota_M3)...because M3 represents JUN, which is the 3rd month in the current quarter (APR-MAY-JUN). But when the data loads next month, I want the expression to dynamically represent Sum(Quota_M1), as JUL is the 1st month of that quarter.

/* LOAD today's date */

Let vLoadDate = Date(Now(),'M/D/YYYY');

Would this be done via an expression formula, (and if so, how?), or better handled in the script (and if so, how?).

Thanks, Dan

Re: Need to replace "fixed" variable name with a dynamic one, based on date.

Mon, 23 Jun 2014 23:36:16 GMT

Hi Dan, PFA.

Thanks,

Ram

Re: Need to replace "fixed" variable name with a dynamic one, based on date.

Anonymous — Tue, 24 Jun 2014 03:26:58 GMT

Hi,

Below script may help you to achieve that,

Let vQuotaQuarter = 'Quota_M'&Ceil(Num(Month(vLoadDate))/3);

LOAD

........

If(Match(Month(vLoadDate), 'Jan', 'Apr', 'Jul', 'Oct'), Quota_M1,

If(Match(Month(vLoadDate), 'Feb', 'May', 'Aug', 'Nov'), Quota_M2,

If(Match(Month(vLoadDate), 'Mar', 'Jun', 'Sep', 'Dec'), Quota_M3) as $(vQuotaQuarter)

Re: Need to replace "fixed" variable name with a dynamic one, based on date.

jagan — Tue, 24 Jun 2014 04:12:13 GMT

Hi Dan,

Try this sample script

tab:

load * inline

[

quota_m1, quota_m2, quota_m3

1,2,3

4,5,6

7,8,9

10,11,12

13,14,15

16,17,18

];

Let vCurrentMonthInQuarter = Month(Today()) - Month(QuarterStart(Today())) + 1;

tab1:

load

Pick($(vCurrentMonthInQuarter), quota_m1, quota_m2, quota_m3) as Quota

resident tab;

Drop table tab;

Regards,

Jagan.

Re: Need to replace "fixed" variable name with a dynamic one, based on date.

Tue, 24 Jun 2014 05:36:47 GMT

=If(Month(Today())='Jan' or Month(Today())='Apr' or Month(Today())='Jul' or Month(Today())='Oct',Sum(Quota_M1),

if(Month(Today())='Feb' or Month(Today())='May' or Month(Today())='Aug' or Month(Today())='Nov',Sum(Quota_M2),

if(Month(Today())='Mar' or Month(Today())='Jun' or Month(Today())='Sep' or Month(Today())='Dec',Sum(Quota_M3))))

Replace Today() with your load date