Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
darrellbutler
Contributor

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

Re: Crosstable & Date Formatting

Could you please share sample excel ?

Not applicable

Re: Crosstable & Date Formatting

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

Thanks

jason_michaelid
Honored Contributor II

Re: Crosstable & Date Formatting

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

shaik_basha
Contributor III

Re: Crosstable & Date Formatting

Hi,

can i get a test data.

Regards

shaik

darrellbutler
Contributor

Re: Crosstable & Date Formatting

Guys,

many thanks for the interest - here goes.

josephinetedesc
Contributor III

Re: Crosstable & Date Formatting

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

Jo

Not applicable

Re: Crosstable & Date Formatting

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

Re: Crosstable & Date Formatting

Also Have a look at this document:

QlikView Date fields

Have a nice weekend Josephine!

stabben23
Honored Contributor

Re: Crosstable & Date Formatting

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.

Community Browser