Qlik Community

Ask a Question

App Development

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

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
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!