Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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, '@', '$'
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, '@', '$'
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.
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!
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.
Ok, I see now.