2 Replies Latest reply: Dec 31, 2011 4:52 AM by Erica Whalley RSS

    what does the % sign stand for? and how to build a Datekey /concatenate two datefields

    Göran Hofstedt

      After a bit reading and some usefull inputs I have have understod I must create a datekey for use in a calender. I was told to create a %datekey so my first question is what does the % sign stands for? temp maybe?

       

      How do I concatenate StartDate and EndDate in the form of 2011-12-01_2011-12-31 and make it as %DateKey? The Start and End-date are from the same table,  the table name is "insats". 

       

      The Load script is

      Insats:

      LOAD Date(Date#FromDate, 'YYYYMMDD'), 'YYYY-MM-DD') as StartDate,   //This is the only way i get QV to set the field as a date-field

                Date(Date#TomDate, 'YYYYMMDD'), 'YYYY-MM-DD') as EndDate

       

      FROM ...insats.....

       

       

       

      Mery x-mas

        • what does the % sign stand for? and how to build a Datekey /concatenate two datefields
          rohit gupta

          hi

          %sign does not has any

          meaning. instant of using % you can use #,$...or whatever you want..this for idenfiny your key or linking key

          for making %date key

          try this

          tab1:

          LOAD Date(Date#FromDate, 'YYYYMMDD'), 'YYYY-MM-DD') as StartDate, 

                    Date(Date#FromDate, 'YYYYMMDD'), 'YYYY-MM-DD') as  %datekey,

                    Date(Date#TomDate, 'YYYYMMDD'), 'YYYY-MM-DD') as EndDate

           

          FROM ...insats.....

          concatenate

          load

          Date(Date#TomDate, 'YYYYMMDD'), 'YYYY-MM-DD') as %datekey

          resident  tab1;

          may it helps you

          thanks

          rohit

            • Re: what does the % sign stand for? and how to build a Datekey /concatenate two datefields

              Hi Hofstedt.

               

              Rohit is correct, the % sign doesn't have any meaning. If you have seen this in a previous example of someone else's work for a manually created key field, my guess is that they were using the '%' sign to make the system recognise it as a hidden field. I use this so the fields then do not appear in any "current selection" boxes, confusing the end user.

               

              To set '%' (or any character '£', '@' etc) as the character at the start of ahidden field, use the following syntax at the start of the script:

              set HidePrefix='%' ;


              '$' is the default prefix used by Qlikview to recognises system fields eg $field, $table

               

              To concatenate the two fields just use the '&' operator:

              Date(Date#FromDate, 'YYYYMMDD'), 'YYYY-MM-DD') &'_' & Date(Date#TomDate, 'YYYYMMDD'), 'YYYY-MM-DD') as %datekey,

               

              You also may be able to simplify your script by changing the date format variable at the start. In my version of qlikview this is set as:

              SET DateFormat='DD/MM/YYYY';

               

              If you change it to:

              SET DateFormat='YYYYMMDD';


              This will enable qlikview to recognise strings in the format 'YYYYMMDD' as dates... hopefully this will work and you won't need to use the date() function to recognise dates.

               

              Your script would then look like

               

              .....

              SET DateFormat='YYYYMMDD';

              ......

              set HidePrefix='%' ;

              ......

              [Load more tables]

              ....

              Insats:

              LOAD

              Date#FromDate as StartDate,

              Date#TomDate as EndDate,

              Date(Date#FromDate, 'DD-MM-YYYY') & '_' & (Date#TomDate, 'DD-MM-YYYY') as %datekey;

              FROM ...insats.....

               

              let me know if these pointers help. And merry christmas to you too!

               

              Erica