Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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

To

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?

1 Solution

Accepted Solutions
Not applicable

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

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, '@', '$'

5 Replies
Not applicable

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

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, '@', '$'

morris70
Valued Contributor

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

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.

Not applicable

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

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!

Not applicable

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

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.

morris70
Valued Contributor

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

Ok, I see now.

Community Browser