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
josephinetedesc
Creator III
Creator III

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

Jo

josephinetedesc
Creator III
Creator III

Hi no ... did not help.  something odd

Jo

Not applicable

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 ?

josephinetedesc
Creator III
Creator III

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!

Jo

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

570

570

570

17 March 2015

570

570

570

570

18 March 2015

570

570

570

570