topic Re: Using 'mid' function to extract unusual date format in App Development

Using 'mid' function to extract unusual date format

malradi88 — Sat, 16 Nov 2024 18:22:17 GMT

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

Re: Using 'mid' function to extract unusual date format

millerhm — Tue, 14 Jul 2020 14:18:40 GMT

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'.

Re: Using 'mid' function to extract unusual date format

Kushal_Chawda — Tue, 14 Jul 2020 17:18:47 GMT

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')

Re: Using 'mid' function to extract unusual date format

malradi88 — Wed, 15 Jul 2020 10:06:35 GMT

Thank you very much!

Re: Using 'mid' function to extract unusual date format

malradi88 — Wed, 15 Jul 2020 10:06:54 GMT

Thank you very much!