date# enigma

[Levente_Szittya]

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

[marcus_sommer]

What happens with?

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

- Marcus

[Levente_Szittya]

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 · Accepted solution]

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]

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