topic Re: Variables from an external File: Trouble with referencing variables within a variable in QlikView

Variables from an external File: Trouble with referencing variables within a variable

Tue, 17 Dec 2013 15:21:08 GMT

I’m attempting to load all of my Variables from an external file. Basically to make sure all developers are defining their kpi’s in the same way. I’ve created a text file, which I’ve stored my variables, and I am loading them via an INCLUDE statement in the Qlikview script.

Here is a sample of my Variable Txt file:

LET vCalendarYrCY=NUM(YEAR(Today()));

LET vCurrentSY_Y= NUM(IF (Month(Today())>= 9,YEAR(Today())+1,Year(Today())));

SET vMostRecentSnapshotDate=DATE(MAX({<SalesYear_Year={'$(vCalendarYrCY)'}>} SnapshotDate), 'YYYY-MM-DD');

SET v_SDNow_SYCurr_NetAC=

COUNT({$<ps_NetAC={'>0'}

, SOStatusCode={'AC','TR'}

, SalesOp_TransportMode={'BUS','AIR'}

,SnapshotDate={'$(vMostRecentSnapshotDate)'}

,SalesYear_Year={$(vCurrentSY_Y)}

>}DISTINCT SalesOp_id);

All of these load into the document , vCalendarYrCY and vMostRecentSnapshotDate load exactly as I intended. My issue comes in to play when I attempt to reference vMostRecentSnapshotDate in the next variable v_SDNow_SYCurr_NetAC. For some reason it refuses to pass exactly as it is written and instead passes this:

COUNT({$<ps_NetAC={'>0'}

, SOStatusCode={'AC','TR'}

, SalesOp_TransportMode={'BUS','AIR'}

,SnapshotDate={' DATE(MAX({<SalesYear_Year={'$(vCalendarYrCY)'}>} SnapshotDate), 'YYYY-MM-DD')'}

,SalesYear_Year={2014}

>}DISTINCT SalesOp_id);

This of course does not work, and defeats the purpose of using an external file to Load the variables, as the user will now have to go in and change

SnapshotDate={' DATE(MAX({<SalesYear_Year={'$(vCalendarYrCY)'}>} SnapshotDate), 'YYYY-MM-DD')'}

SnapshotDate={'$(vMostRecentSnapshotDate)'}

In every area that this variable is referenced. I have tried several things, I have removed quotes:

SnapshotDate={$(vMostRecentSnapshotDate)}

Tried double quotes

SnapshotDate={“$(vMostRecentSnapshotDate)”}

Neither of these accomplished what I need to happen. Has anyone run into this? If so have you found a workaround?

Re: Variables from an external File: Trouble with referencing variables within a variable

Tue, 17 Dec 2013 15:28:05 GMT

Try below

LET vCurrentSY_Y= NUM(IF (Month(Today())>= 9,YEAR(Today())+1,Year(Today())));

changed the $ to @. Just to confuse Qlikview

SET vMostRecentSnapshotDate=DATE(MAX({<SalesYear_Year={'@(vCalendarYrCY)'}>} SnapshotDate), 'YYYY-MM-DD');

then use Replace function to

LET vMostRecentSnapshotDate = Replace (vMostRecentSnapshotDate, '@', '$'

Re: Variables from an external File: Trouble with referencing variables within a variable

Anonymous — Tue, 17 Dec 2013 15:33:29 GMT

Hmm, I might be missing something here but are you sure you are using set and let correctly?

Again I just briefly glanced at you variables.

Re: Variables from an external File: Trouble with referencing variables within a variable

Tue, 17 Dec 2013 15:40:23 GMT

Definitely works, I'm wondering if there is a less labor intensive way as I am dealing with 100's of variables, but this can definitely solve my issue! Thanks Sudheer!

Re: Variables from an external File: Trouble with referencing variables within a variable

Tue, 17 Dec 2013 15:42:11 GMT

Pretty sure I am Nils, as the LET statements are working as I am intending, which means each time I load, they hard code a value into the variable, and not a formula.

Re: Variables from an external File: Trouble with referencing variables within a variable

Anonymous — Tue, 17 Dec 2013 15:47:14 GMT

Ok, I see now.