topic Re: format issue with date in QlikView

format issue with date

Anonymous — Thu, 21 Jul 2016 21:44:19 GMT

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

Re: format issue with date

Thu, 21 Jul 2016 22:29:45 GMT

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

Re: format issue with date

maksim_senin — Thu, 21 Jul 2016 23:42:42 GMT

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

Re: format issue with date

rwunderlich — Fri, 22 Jul 2016 00:38:53 GMT

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

Re: format issue with date

Anonymous — Fri, 22 Jul 2016 02:42:31 GMT

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.

Re: format issue with date

Fri, 22 Jul 2016 08:08:20 GMT

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!

Re: format issue with date

Anonymous — Fri, 22 Jul 2016 13:13:49 GMT

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.

Re: format issue with date

Anonymous — Fri, 22 Jul 2016 13:30:30 GMT

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

Re: format issue with date

Tue, 26 Jul 2016 06:10:45 GMT

Hi QlikRajan,

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