Convert String (202101) to Date Format (2021-01-01)- Qlik Sense

[manisha10]

Hi All, 

I have a table with a column called Date with these values:  202101, 202102, 202103. I want to convert these values into YYYY-MM-DD. 

Example: 202101 should be 2021-01-01 and 202102 should be 2021-02-01

Where should the formula be written? 

Finally, I want to join Table A (Date - 2021-01-01) with Table B (Date- 202101) with Date column.

Thank you

Qlik Sense Business Qlik Cloud Data Services Qlik Sense Desktop 

[Taoufiq_Zarra]

@manisha10  Maye be

SET DateFormat='YYYY-MM-DD';

you can use this in script or UI

Date(Date#(Date&'01','YYYYMMDD'))

in Script for example :

LOAD *,Date(Date#(Date&'01','YYYYMMDD')) as NewDate INLINE [
Date
202101
202102
202103
];

output:

[manisha10]

Hi Taoufiq, 

Thank you for the quick response! Should I create a new sub section and write this code there? 

My tables A and B are already loaded. 

[Taoufiq_Zarra]

Hi,

not necessarily, you can just use

Date(Date#(Date&'01','YYYYMMDD'))

in your expressions

[tresB]

Hi @Taoufiq_Zarra ,

We don't need to mention day explicitly in parsing function. You can just leave that part and by default qlik would consider day 1 of the month. Try like:

Date(Date#('202101', 'YYYYMM'))

[Taoufiq_Zarra]

Hi @tresB 

Thanks 😉👍