Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
darrellbutler
Creator
Creator

Crosstable & Date Formatting

Hi, I'm loading an excel spreadsheet that has dates as columns from left to right.

I'm converting this to a flat table using the crosstable function. However, this just converts the dates to microsoft excel time code.

I've done a resident load from the crosstable load,  trying to convert my date column by using a combination of date# and date.

However the dates under the date column still remain as text which limits the usefulness of my script.

Has anyone got any clues.

Thanks and regards.

13 Replies
Not applicable

Could you please share sample excel ?

Not applicable

Can you provide an example so that it will helpful for us to reply.

Thanks

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Please prvode sample data - I'm sure it can be fixed quite easily if we can see exactly what you're dealing with!

Anonymous
Not applicable

Hi,

can i get a test data.

Regards

shaik

darrellbutler
Creator
Creator
Author

Guys,

many thanks for the interest - here goes.

josephinetedesc
Creator III
Creator III

I am having the same problem - was anyone able to help?

Jo

Not applicable

Hello Josephine,

I don't know if this will help you out.

In my case, I had to retrieve data from an Excel file. The format of the date in Excel, was just the 'Month-Year' (08-2014) only.

On the main page of the Data Load Editor I changed the SET DateFormat='MM-YYYY';

and in my script:

Quantity:

Crosstable (MonthYear,Quantity,2) Load *

FROM [lib://Zen/CHINA720810c.xls]

(biff, embedded labels, table is [quantity 1$]);

Hope this help!

Not applicable

Also Have a look at this document:

QlikView Date fields

Have a nice weekend Josephine!

stabben23
Partner - Master
Partner - Master

Hi, I think I found the problem, rename your "Date" in CrossTable to something else, for ex Datefield

HS:

CrossTable(Datefield, Data, 3)

LOAD TYPE,

     [Sub Type],

     SITE,

     [41275],

     [41306],

     [41334],

     [41365],

     [41395],

     [41426],

     [41456],

     [41487],

     [41518],

     [41548],

     [41579],

     [41609]

FROM

[\\UKFM2003FAP1\Departmental Shared Folders\Health and Safety\SHE PERIOD REPORTS\2013 GROUP SHE PERIOD REPORT FORMAT KPI.XLSX]

(ooxml, embedded labels, table is [Qlikview Import]);

and then just do this

date(Datefield) as  Date

Seams that qlikview have problem when fieldname is Date in CrossTable.