Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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

View solution in original post

5 Replies
Not applicable
Author

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

Anonymous
Not applicable
Author

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
Author

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
Author

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.

Anonymous
Not applicable
Author

Ok, I see now.