Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
linoyel
Specialist
Specialist

Load date from oracle number

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.

Labels (1)
1 Solution

Accepted Solutions
linoyel
Specialist
Specialist
Author

@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!

View solution in original post

4 Replies
Taoufiq_Zarra

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 
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
linoyel
Specialist
Specialist
Author

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.

Taoufiq_Zarra

@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.

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
linoyel
Specialist
Specialist
Author

@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!