format issue with date

[Anonymous]

Hey guys,

I am facing some strange issues with date. I am loading data from a spreadsheet which have dates in number format and then concatenate with existing table in qlikview. I get '?' for some date values. When I apply Date() in text object that '?' converts to correct date . My qlikview is 11.2 SR 14.

Please find the image attached.

Any help is much appreciated.

Thanks,

Rupesh

[#2]

you can use the function date(youdate,'YYYY/MM/DD')

[maksim_senin]

Hi,

In order to understand that in depth please read HIC's blogs, which really help:

1. The Date Function

2. QlikView Date fields

3. https://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right

4. https://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

5. and this post - difference between Date and Date# function

Hope this helps.

Best regards,

Maxim

[rwunderlich · Accepted solution]

I've run across that occasionally when doing unusual things like loading with FieldValues() function.  QV somehow doesn't know what to do for a display format. The internal value is ok as you've seen, you just need to apply a Date() format to it

-Rob

[Anonymous]

I believe I am facing the similar issue. I was able to get the right output but logic for this occurrence was not clear to me. Now. much to my comfort I am not alone with this experience. Tomorrow, I will explain you in detail in which condition I was getting '?'. Thanks Rob for sharing your insight.

[#6]

Hi QlikRajan,

The format comes in your spread sheet is sql by default format.when you use same values in visualization so it comes with special character i.e "?".

So its better you use Date function to convert values as compatible for visualization to Qlik at back end.

Please Mark my comment helpful if it helped you somehow!

[Anonymous]

Rohit - This is not exactly the case. Marking helpful might mislead other solution seekers who come across the similar issue.It was not a challenge to correct the date values but to know the reason of that inconsistency.

[Anonymous]

In my application data is coming from 3 sources-

1. 13,518 different spreadsheets which have different date formats that I streamlined using a combo of alt, date#, date, num# functions (no problem with this part)

2. source 1 is concatenated with source 2 which has data stored in a qvd. Date format is set and I don't have any issues until here.

3. A third spreadsheet is concatenated with source 1 and 2. This spreadsheet has date in M/D/YYYY format. When I just upload and concatenate this data, 20-30% values (not all) show as '?'. I fixed this issue converting all the values  in text first and then using date#, num functions. That resolved the issue but what I did not understand-

a. why qlikview don't read all the dates correctly when I load and concatenate it with other existing data. It's worth mentioning here that loading just third data source in a separate qvw does not produce any '?'

b. how qlikview is interpreting something unreadable to '?' .

Thanks

[#9]

Hi QlikRajan,

Can you please attach the snapshot where "?" is coming in visuals secondly?