Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
malradi88
Creator II
Creator II

Using 'mid' function to extract unusual date format

To Whom it May Concern,

I am currently trying to extract a date from the following ID format:

2720815240xxxx   - the date in this ID is represented by the 2nd to 7th digits 72-08-15   (YY/MM/DD) August 15th 1972

3041009240xxxx   - the date in this ID is represented by the 2nd to 7th digits 04-10-09   (YY/MM/DD) October 10th 2004

I was able to extract the relevant numbers via the following code: =mid([ID],2,6)

Following this step I was unable to obtain correct dates. I tried the following expression: =DATE(mid([ID],2,6),'YY/MM/DD')

and got results in date format that were incorrect. For example the above-mentioned 2720815240xxxx  became 73/07/09. 

Is there anyway to work with this format? basically anything beginning with 2 means the person was born in the 20th century, and anything beginning with 3 the 21st century. This is the Egyptian ID system. 

Thank you for your support!

Best,

Mohammed

 

 

Labels (6)
2 Solutions

Accepted Solutions
millerhm
Partner
Partner

well, hey - you learn something new every day! Thanks for telling us about the date Egyptian format.

I think you likely need to tell Qlik that the value you're extracting is a date before formatting it. Have you tried 

=DATE(Date#(mid([ID],2,6),'YYMMDD'),'YY/MM/DD')

That should tell Qlik that the value you've extracted is a date in the format YYMMDD and you want it to then be displayed as YY/MM/DD'. 

View solution in original post

Kushal_Chawda

You need to provide the same format as is using date# to recognize it as Date and then use Date function on top of that

date(DATE#(mid([ID],2,6),'YYMMDD'),'YY/MM/DD')

View solution in original post

4 Replies
millerhm
Partner
Partner

well, hey - you learn something new every day! Thanks for telling us about the date Egyptian format.

I think you likely need to tell Qlik that the value you're extracting is a date before formatting it. Have you tried 

=DATE(Date#(mid([ID],2,6),'YYMMDD'),'YY/MM/DD')

That should tell Qlik that the value you've extracted is a date in the format YYMMDD and you want it to then be displayed as YY/MM/DD'. 

View solution in original post

Kushal_Chawda

You need to provide the same format as is using date# to recognize it as Date and then use Date function on top of that

date(DATE#(mid([ID],2,6),'YYMMDD'),'YY/MM/DD')

View solution in original post

malradi88
Creator II
Creator II
Author

Thank you very much! 

malradi88
Creator II
Creator II
Author

Thank you very much!