topic Re: Ongoing Issue with Dates in QlikView

Ongoing Issue with Dates

Thu, 15 Jan 2015 05:48:15 GMT

I have had this problem before and no amount of searching gets me to the answer, I am hoping someone can shed some light here for me. I have seen some similar posts go unanswered so I am hoping this one can be resolved for me once and for all!

I have a simple spreadsheet with just two fields - ID and Date. When I create a pivot chart I can see the dates in full date/time format. However no matter how I try to group by Month it returns nothing at all.

If I open the file in Excel (csv file) and create a pivot I can easily group as Month so the format is clearly ok. I have this issue before and it really pains me as I just cant see anything wrong with the data. Other very similar csv files work as expected.

Does anyone know why this is happening?

Message was edited by: Darren Martin Does this help?

Re: Ongoing Issue with Dates

Thu, 15 Jan 2015 05:55:01 GMT

Have you tried including a monthstart(datefield) as month to your load script...then build the pivot based on that as a dimension and a count (ID)

Re: Ongoing Issue with Dates

jonathandienst — Thu, 15 Jan 2015 05:59:51 GMT

There is only 1 date in the attached data file. I suggest that you upload the QVW model that is not working for more useful help.

Jonathan

Re: Ongoing Issue with Dates

Thu, 15 Jan 2015 06:03:41 GMT

Yes, doesn't work.

Re: Ongoing Issue with Dates

Thu, 15 Jan 2015 06:05:00 GMT

But t should still work, even if only one date. It works with other data and with a pivot in excel. I( cant upload the QVW as I am not licensed yet.

Re: Ongoing Issue with Dates

jonathandienst — Thu, 15 Jan 2015 06:08:57 GMT

You can upload the qvw you created without a license. I have a license, so I will be able to open what you upload.

Re: Ongoing Issue with Dates

Thu, 15 Jan 2015 06:10:05 GMT

Your date is showing as a text field...use the following script to convert

Monthstart(Date#(Date,'DD/MM/YY h:mm')) as Month

Happy Qliking!!

Re: Ongoing Issue with Dates

jonathandienst — Thu, 15 Jan 2015 06:24:28 GMT

Use this for your load script:

LOAD *,

Month(Date) As Month,

Year(Date) As Year

;

LOAD ID,

Date(Floor(Date#(Date, 'D/M/YYYY h:mm'))) As Date,

Time(Frac(Date#(Date, 'D/M/YYYY h:mm'))) As Time

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

* floor the date to remove the time component

* put the time in a separate field

* add calendar fields (Month and Year) --- check manual/search community for "preceding load" for more info

HTH

Jonathan

Re: Ongoing Issue with Dates

Anonymous — Thu, 15 Jan 2015 06:55:42 GMT

Seems your issue resolved ,

Please find attached qvw .. let me know if this is not correct solution

Re: Ongoing Issue with Dates

Thu, 15 Jan 2015 08:23:03 GMT

Thanks guys. Both solutions work on my sample but neither works on my actual large database which I cannot share unfortunately. Basically, all files are same format, however only 2 of them work. I will spend more time on it tomorrow but cant figure it out. Thanks for your help!