Dates show up as text instead of dates

[#1]

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;

[petter]

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...

[MarcoWedel]

CrossTable() treats fieldnames as text.

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

hopw this helps

regards

Marco

[#4]

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]

Had exactly this problem... pretty frustrating.

[juleshartley]

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