Date field issue

[pauldamen]

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

[pauldamen]

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?

[Gysbert_Wassenaar]

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;

[pauldamen]

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

[Gysbert_Wassenaar · Accepted solution]

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.

[pauldamen]

Great thanks for your help!!