topic Re: Date field issue in QlikView

Date field issue

pauldamen — Tue, 26 Feb 2013 10:23:34 GMT

Dear all,

I have loaded a date field from excel into Qlikview. I had to use a crosstable since the dates are in the header in Excel.

But when I load it and try to make a calendar it doesn't work.

I think it is because it doesn't recognize the date field (I tried to only make a min and max date and it comes up empty)

I think it has something to do with the crosstable. Could someone help me with what I am doing wrong?

I have attached the excel and QVD.

Thanks for your help!

Regards,

Paul

Re: Date field issue

pauldamen — Tue, 26 Feb 2013 10:58:22 GMT

The thing is that the listbox of the date field already shows the numbers as displayed in Excel (40000 etc.) but I can change it in to whatever I want (timestamp, date) it doesn't change.

When I add a colum in my Excel and add some dates it works. So it is something with the crosstable and not recognizing the dates moved.

Any ideas?

Re: Date field issue

Gysbert_Wassenaar — Tue, 26 Feb 2013 14:33:10 GMT

Crosstable creates a text field from the column names. So while the values look like numbers, they are text. You can use the num# function to convert them to numbers.

TempData:

CrossTable(Date, Bedrag, 6)

LOAD Code,

[Business Unit],

Groep,

KPI,

[KPI Naam],

Scenario,

40544,

40575,

...etc...

Data:

noconcatenate LOAD

Code, [Business Unit], Groep, KPI, [KPI Naam], Scenario, Bedrag,

num#(Date) as Date

Resident TempData;

drop table TempData;

Re: Date field issue

pauldamen — Thu, 28 Feb 2013 08:55:51 GMT

I managed to get the date field to work.

Only problem now is that the field "Bedrag" (Amount) is not recognized anymore and is also transferred into date numbers instead of the euro amount it contained.

Any ideas?

Regards,

Paul

Re: Date field issue

Gysbert_Wassenaar — Thu, 28 Feb 2013 09:11:01 GMT

You need to enclose the field names that are numbers in square brackets (or double quotes). Otherwise Qlikview will use the number as the literal value for the field.

Re: Date field issue

pauldamen — Thu, 28 Feb 2013 09:16:18 GMT

Great thanks for your help!!