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: 
Qliksense_77
Creator
Creator

Convert datefield

Hi,

I have a date field where dec 2020 is in this format: 2020M12 and so on.

The same date field also contains one date in this format DD/MM/YYYY which is the newest date in the data.

I want to convert all dates to this format DD/MM/YYYY.

How to do this?

The reason for this, is that I need to show what is status now compared to same month last year. And I know how to do this, if all dates are in the standard date format.  

1 Solution

Accepted Solutions
Taoufiq_Zarra

@Qliksense_77 

If I suppose 2020M12 wil be 01/12/2020 you can use :

Date(Alt(Num(makedate(subfield(DateField,'M',1),subfield(DateField,'M',2),01)), Num(Date#(DateField, 'DD/MM/YYYY'))), 'DD/MM/YYYY') as NewDateField 

like this :

 

Data:

load *,Date(Alt(Num(makedate(subfield(DateField,'M',1),subfield(DateField,'M',2),01)), Num(Date#(DateField, 'DD/MM/YYYY'))), 'DD/MM/YYYY') as NewDateField inline [
DateField
2020M12
2020M10
2020M01
11/12/2020
01/01/2021
]

 

output:

Capture.PNG

if 2020M12 for example not 01/12 but 31/12 or other date you can change 1 by your number

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉

View solution in original post

4 Replies
Taoufiq_Zarra

@Qliksense_77 

If I suppose 2020M12 wil be 01/12/2020 you can use :

Date(Alt(Num(makedate(subfield(DateField,'M',1),subfield(DateField,'M',2),01)), Num(Date#(DateField, 'DD/MM/YYYY'))), 'DD/MM/YYYY') as NewDateField 

like this :

 

Data:

load *,Date(Alt(Num(makedate(subfield(DateField,'M',1),subfield(DateField,'M',2),01)), Num(Date#(DateField, 'DD/MM/YYYY'))), 'DD/MM/YYYY') as NewDateField inline [
DateField
2020M12
2020M10
2020M01
11/12/2020
01/01/2021
]

 

output:

Capture.PNG

if 2020M12 for example not 01/12 but 31/12 or other date you can change 1 by your number

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉
Qliksense_77
Creator
Creator
Author

@Taoufiq_Zarra 

Thank you for fast reply.

Now I have this problem:

 6c9ed7e42f89433ab42593bead078631.png

Taoufiq_Zarra

@Qliksense_77 

this problem is not related to date format problems,but seems to be in the modeling or the calculation of measure

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉
Qliksense_77
Creator
Creator
Author

@Taoufiq_Zarra 

You are right. Your solution work. Thanks a lot😀