Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Dates show up as text instead of dates

Hi!

So I've concatenated two cross tables with a regular table. The first column is supposed to be the dates. It's working on the first cross table and the regular table, but not on the second cross table. It's reading the dates as Excel general text. I attached a picture of the table viewer and will copy the script. Thanks for all your help!

Concur:

LOAD Capitalize([Last Name])as [Last Name],

      [Sent for Payment Date] as DATE,

     [Approved Amount]

   FROM

(ooxml, embedded labels, header is 2 lines, table is Page1_1);

Concatenate (Concur)

Concur2014:

LOAD Capitalize([Last Name])as [Last Name],

    [Sent for Payment Date] as DATE,

     [Approved Amount]

FROM

(ooxml, embedded labels, header is 2 lines, table is Page1_1);

Concatenate (Concur)

Concur2015:

LOAD Capitalize([Last Name])as [Last Name],

     [Sent for Payment Date] as DATE,

     [Approved Amount]

   FROM

(ooxml, embedded labels, header is 2 lines, table is Page1_1);

Parts_TEMP:

CrossTable([Last Name], [Parts AOP Data])

LOAD DATE,

     LastNameCr,

     LastNameRo,

    LastNameAp,

     LastNameGu,

     LastNameWh,

     LastNameGr,

     LastNamePe,

     LastNameRe,

     LastNameHi,

     LastNameTu

FROM

(ooxml, embedded labels, table is [By name]);

PHH_TEMP:

CrossTable(DATE, PHHData, 1)

LOAD   [Last Name],

     [41640],

     [41671],

     [41699],

     [41730],

     [41760],

     [41791],

     [41821],

     [41852],

     [41883],

     [41913],

     [41944],

     [41974]

FROM

(ooxml, embedded labels, header is 4 lines, table is PHH);

NoConcatenate

END_TABLE:

LOAD *

RESIDENT  Parts_TEMP;

Concatenate(END_TABLE)

load *

Resident PHH_TEMP;

Concatenate(END_TABLE)

load *

Resident Concur;

drop tables Parts_TEMP,PHH_TEMP,Concur;

5 Replies
MVP
MVP

Re: Dates show up as text instead of dates

Actually I have experienced the same problem with dates and CrossTable... CrossTable is quite sensitive to a numerous of issues. So what I did was to do a resident load on the result of a CrossTable and applying explicit date conversion there because the CrossTable wouldn't do as I expected it to...

Re: Dates show up as text instead of dates

CrossTable() treats fieldnames as text.

you could left join a field that converts the fieldnames to dates.

hopw this helps

regards

Marco

Not applicable

Re: Dates show up as text instead of dates

I tried this:

Concatenate(END_TABLE)

load [Last Name],

Date(PHHDATE) as DATE,

PHHData

Resident PHH_TEMP;

And it just nulled out the date field for PHHData. Do you know why or have another suggestion? Thank you!

juleshartley
Valued Contributor

Re: Dates show up as text instead of dates

Had exactly this problem... pretty frustrating.

juleshartley
Valued Contributor

Re: Dates show up as text instead of dates

Num#([DateTextField]) worked for me...