Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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