Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Levente_Szittya
Partner - Contributor III
Partner - Contributor III

date# enigma

Hi,

I have an excel data source where a particular field contains a date, but stored as a TEXT displayed in 'YY.MM.DD' format.

When I import the source file and use date# function, then it seems that date# function does not recognise this format, although it is explicitly told to Qlik that we want this field's text value to be evaluated by using 'YY.MM.DD' date mask:

date#(field,'YY.MM.DD') as datum_dateformat

Why does it happen? How can this be solved?

Thanks in advance

Levente

Levente

Labels (2)
1 Solution

Accepted Solutions
marcus_sommer

Yes, it could be that there any other invisible chars and/or the data respectively chars are different to your expectation. Probably there is no general way to ensure that the data are always interpreted/converted/formatted like you want.

I think I would in this case trying something like this:

date(date#(text(field), 'pattern'), 'pattern')

or maybe

date(date#(keepchar(field, '0123456789'), 'pattern'), 'pattern')

If there multiple pattern possible you could add them to an alt(). If those measures didn't help I would probably check the length with len() and returning all char-information from the value, for example with something like:

t:
load mid(field, iterno(), 1) as Char, ord(mid(field, iterno(), 1)) as CharNumber,
           recno() as RecNo, iterno() as IterNo
from X while iterno() <= len(field);

- Marcus

View solution in original post

4 Replies
marcus_sommer

What happens with?

date(date#(field,'YY.MM.DD')) as datum_dateformat

- Marcus

Levente_Szittya
Partner - Contributor III
Partner - Contributor III
Author

Hi Marcus,

It remains text - at least it does not turn into "date" (date value to be precise)... So when I create an expression saying date(datefield) then I get back a null value.

The real strange thing is that I could not reproduce the error... I have created a txt with only one date column and one record:

datetext
21.11.03

Then I imported it in excel ensuring that it is stored as text.

Then I have created a test app, imported the excel file and this time date# function works fine...

When I create the same expression (date(datefield), where datefield is date#(datetext,'YY.MM.DD')) then the result is the proper date in default dateformat...

How could I ensure that Qlik ony considers those chars that I do see when I edit the excel sheet? Could it be an issue that for some reason the imported format contains something hidden from my eyes preventing Qlik to assess it being equivalent with the expected YY.MM.DD format?

Levente

 

 

marcus_sommer

Yes, it could be that there any other invisible chars and/or the data respectively chars are different to your expectation. Probably there is no general way to ensure that the data are always interpreted/converted/formatted like you want.

I think I would in this case trying something like this:

date(date#(text(field), 'pattern'), 'pattern')

or maybe

date(date#(keepchar(field, '0123456789'), 'pattern'), 'pattern')

If there multiple pattern possible you could add them to an alt(). If those measures didn't help I would probably check the length with len() and returning all char-information from the value, for example with something like:

t:
load mid(field, iterno(), 1) as Char, ord(mid(field, iterno(), 1)) as CharNumber,
           recno() as RecNo, iterno() as IterNo
from X while iterno() <= len(field);

- Marcus

Levente_Szittya
Partner - Contributor III
Partner - Contributor III
Author

Hi Marcus,

thanks for the advices - I will use them and try to figure out what caused the unexpected non-date results.

Best regards,

Levente