Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jasmeenkr
Contributor
Contributor

Date from Datetime in format YYYYMMDD

Hi All,

 

I am trying to load date from datetime using script:

LOAD
Date(DateTime,'YYYYMMDD') as DateKey,

 

but it gives me value as integer 43256 and i want data to be YYYYMMDD.

 

 

2 Solutions

Accepted Solutions
dilipranjith
Partner
Partner

in your load script try something like below

 

Date(Date#(DateTime,'d/m/yyyy hh:mm:ss TT'),'YYYYMMDD') as DateKey

 

View solution in original post

dilipranjith
Partner
Partner

If it's already a date number you don't need date# function.

Just the date function and format you need

View solution in original post

7 Replies
dilipranjith
Partner
Partner

give an example of values in DateTime in source

jasmeenkr
Contributor
Contributor
Author

The values are like 2/3/2019 12:00:00 AM

dilipranjith
Partner
Partner

in your load script try something like below

 

Date(Date#(DateTime,'d/m/yyyy hh:mm:ss TT'),'YYYYMMDD') as DateKey

 

View solution in original post

jasmeenkr
Contributor
Contributor
Author

Thanks. This does resolved issue but now I have to join it with another table where datekey is defined as bigint values 20190903. how to convert it into date so that it joins to Fact.DateSK

dilipranjith
Partner
Partner

Similar process using date# and date but different format s.

Date(date#(column-name, 'source forma'), 'desired format')

If you want to link between tables you will need to add floor to my previous answer to remove time

E.g. date(floor(date#(...))....)

jasmeenkr
Contributor
Contributor
Author

Hi ,,

 

So my DImDate has dateKey -datatype- bigint

Fact Order has Datekey from Date(Date#(Datetime,'m/d/yyyy hh:mm:ss'),'YYYYMMDD') as integer value like 43754 in place of datekey like 20140305. how to correct this so my tables connect and my data relatable between two

dilipranjith
Partner
Partner

If it's already a date number you don't need date# function.

Just the date function and format you need

View solution in original post