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

[#1]

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

[rohit214]

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

[#3 · Accepted solution]

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