Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Could you please share sample excel ?
Can you provide an example so that it will helpful for us to reply.
Thanks
Please prvode sample data - I'm sure it can be fixed quite easily if we can see exactly what you're dealing with!
Hi,
can i get a test data.
Regards
shaik
Guys,
many thanks for the interest - here goes.
I am having the same problem - was anyone able to help?
Jo
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!
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.