Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have a date in Oracle which is stored in an 8 digits number format, e.g. 20200716 and I need to load it into Qlik Sense in a date format: 16/07/2020. The regular formula for this (Load Date(Date#(MyNumericField,'YYYYMMDD'),'DD/MM/YYYY') as Date) doesn't work because it brings it with $numeric and $integer formats, while I need it to be tagged a s a timestamp.
@Taoufiq_Zarra , I tried the resident load as well but still - no date tag. The solution was to explicitly tag this field as a date: TAG MyDateField with $Date. Thank you for all your help and suggestions!
Hi,
normally (Load Date(Date#(MyNumericField,'YYYYMMDD'),'DD/MM/YYYY') as Date) will work
but you can always do that.
Makedate(num(left(MyNumericField,4)),num(right(left(MyNumericField,6),2)),num(right(MyNumericField,2))) as Date
Thanks @Taoufiq_Zarra but what you suggested achieves the same functionality as a regular formula, meaning that the final field looks as date and I can perform date actions on it like Year(Date), but I still need it to be tagged as date / timestamp by qlik sense so I'll be able to use this field in a date picker calendar extension, and it's still tagged as numeric and integer. I think this issue is connected to Oracle data source.
@linoyel, Okay, I see the problem.
Unfortunately I don't use that extension.
but once the field is loaded in date format with Date# or makedate in the "LOAD" script it is loaded in the internal table of qlik , so the relation with Oracle is finished.
in my opinion it is necessary to look in the format requested for the date is this DD/MM/YYYY or MM/DD/YYYYY (Extension)
and to be sure you can also do this just to make sure it's not Oracle:
Tmp:
Load
Numericalfield,
...
SQL select from Oracle;
then
Table:
noconcatenate
load Date#() as ... resident Tmp;
drop table Tmp;
now you have no relation with oracle data source.
@Taoufiq_Zarra , I tried the resident load as well but still - no date tag. The solution was to explicitly tag this field as a date: TAG MyDateField with $Date. Thank you for all your help and suggestions!