Ongoing Issue with Dates

[#1]

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?

[#2]

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)

[jonathandienst]

Hi

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

[#4]

Yes, doesn't work.

[#5]

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.

[jonathandienst]

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

[#7]

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!!

[jonathandienst]

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

[Anonymous]

Hi

Seems your issue resolved ,

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

[#10]

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!