Reformat of dates in load script

[#1]

I am trying to load several date fields from Infor XA into Qlikview through ODBC.  The dates are coming in with this format:

1150115

I am not sure what the first digit represents.  Digits 2&3 are the year, 4&5 are the month and 6&7 are the day.

How can I convert this to the following format:  01/15/2015  using the load script?

[#2]

Ok, we're getting closer.  Fixed that issue, now I get this error:

Using this script:

ODBC

CONNECT32 TO [SKFVAB01 XA 7J] (XUserId is UKVUeSZORJMITcUH, XPassword is dNNCZWFMSDdSGXRMZZKB);

//-------- Start Multiple Select Statements ------
SKFVAB01.AMFLIBJ.MOMAST:
LOAD *,
Date(Date#(RIGHT(ActStart,6),'YYMMDD'),'MM/DD/YYYY') AS NEW_MDATE,
Date(Date#(RIGHT(DueDate,6),'YYMMDD'),'MM/DD/YYYY') AS NEW_ODUDT;
SQL SELECT FITEM as PartNo,
ORDNO as MO#,
MDATE as ActStart,
ODUDT as DueDate,
OPCUR as CurOp,
ORQTY as Qty,
OSTAT as OrdStat,
RATIO as CrtRatio,
UU40MY as ReleaseLot,
UUCAMY as NitLot,
WCCUR as CurFacility
FROM SKFVAB01.AMFLIBJ.MOMAST;

LEFT JOIN (SKFVAB01.AMFLIBJ.MOMAST)

SQL SELECT ITCLS as Class,
ITDSC as ItemDescrpt,
ITNBR as PartNo,
ITTYP as Type
FROM SKFVAB01.AMFLIBJ.ITEMASA;

//-------- End Multiple Select Statements ------

[PrashantSangle]

Hi,

Are you sure do you a field name ActStart in table

Is it ActStart or MDate??

Regards

[#4]

The field name in the table is MDATE, I am trying to rename it to ActStart and convert the format.

[Anonymous]

Is it Oracle?  Try:

MDATE as "ActStart",
ODUDT as "DueDate",

[#6]

I am pulling this data from MAPICS (Infor XA).  If I remove the lines:

LOAD *,
Date(Date#(RIGHT(ActStart,6),'YYMMDD'),'MM/DD/YYYY') AS NEW_MDATE,
Date(Date#(RIGHT(DueDate,6),'YYMMDD'),'MM/DD/YYYY') AS NEW_ODUDT;

from the script above, it runs as fine, renames the fields, but gives me the wrong format in the the MDATE and ODUDT fields.

[Anonymous]

OK, in this case don't do renaming, at least for these two fields on the SQL SELECT side, but rename only on LOAD side:

LOAD *,
Date(Date#(RIGHT(MDATE,6),'YYMMDD'),'MM/DD/YYYY') AS NEW_MDATE,
Date(Date#(RIGHT(ODUDT,6),'YYMMDD'),'MM/DD/YYYY') AS NEW_ODUDT;
SQL SELECT FITEM as PartNo,
ORDNO as MO#,
MDATE,
ODUDT,
OPCUR as CurOp,
ORQTY as Qty,
OSTAT as OrdStat,
RATIO as CrtRatio,
UU40MY as ReleaseLot,
UUCAMY as NitLot,
WCCUR as CurFacility
FROM SKFVAB01.AMFLIBJ.MOMAST;

[#8]

Worked like a charm!  I also changed the "NEW_MDATE" to "ActStart" and it took care of the renaming as well.

Thanks for your help!

kp

[Anonymous]

I think the reason of the last problem was similar to the Oracle, where renaming

MDATE as ActStart

creates field ACTSTART rather than the expected ActStart, hence the field is "not found".