5 Replies Latest reply: Dec 17, 2013 10:47 AM by Nils Morris RSS

    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?