topic Crosstable & Date Formatting in QlikView

Crosstable & Date Formatting

darrellbutler — Sat, 06 Apr 2013 20:03:49 GMT

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.

Re: Crosstable & Date Formatting

Sun, 07 Apr 2013 04:44:12 GMT

Could you please share sample excel ?

Re: Crosstable & Date Formatting

Sun, 07 Apr 2013 04:53:42 GMT

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

Thanks

Re: Crosstable & Date Formatting

Jason_Michaelides — Sun, 07 Apr 2013 10:32:28 GMT

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

Re: Crosstable & Date Formatting

Anonymous — Mon, 08 Apr 2013 07:26:44 GMT

Hi,

can i get a test data.

Regards

shaik

Re: Crosstable & Date Formatting

darrellbutler — Tue, 09 Apr 2013 08:30:07 GMT

Guys,

many thanks for the interest - here goes.

Re: Crosstable & Date Formatting

josephinetedesc — Sat, 11 Apr 2015 14:15:22 GMT

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

Re: Crosstable & Date Formatting

Sat, 11 Apr 2015 18:25:35 GMT

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!

Re: Crosstable & Date Formatting

Sat, 11 Apr 2015 18:56:54 GMT

Also Have a look at this document:

QlikView Date fields

Have a nice weekend Josephine!

Re: Crosstable & Date Formatting

stabben23 — Sat, 11 Apr 2015 20:10:41 GMT

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.

Re: Crosstable & Date Formatting

josephinetedesc — Sun, 12 Apr 2015 09:10:44 GMT

Hi tremblay, no still not working ... but thanks for the reference.

Re: Crosstable & Date Formatting

josephinetedesc — Sun, 12 Apr 2015 09:15:43 GMT

Hi no ... did not help. something odd

Re: Crosstable & Date Formatting

Mon, 13 Apr 2015 08:38:53 GMT

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

HS:

CrossTable(Date, Data, 3)

LOAD *

FROM

(ooxml, embedded labels);

NoConcatenate

TEST:

LOAD

TYPE,

[Sub Type],

SITE,

Data,

date(date#(Date, 'DD/MM/YYYY')) as Date

Resident HS;

DROP Table HS;

Worked for me without problems after switching date#(date to date(date#

Can you give that a try with your excel file ?

Re: Crosstable & Date Formatting

josephinetedesc — Tue, 14 Apr 2015 07:25:26 GMT

Hi Markus

In the end I transposed the data so that the date was on the extreme LHS column – at that stage it worked … odd though!

Directory;

CrossTable(Loc, AvailMins)

LOAD F1 as DateFull,

Mach1,

Mach2,

Mach3

FROM

(ooxml, embedded labels, table is schedMinsNormal);

Mach1

Mach2

Mach3

Mach4

16 March 2015

570

17 March 2015

570

18 March 2015

570