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: 
Not applicable

Reformat of dates in load script

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?

17 Replies
prabhuappu
Creator II
Creator II

Hi,

Use  Date(Date#(right('1150115',6), 'YYMMDD'), 'MM/DD/YYYY')

It will return 01/15/2015

Replace '1150115' with your field name.

Regards,

Prabhu Appu

MarcoWedel

Or


Date(Date#(Mid(datefield,2), 'YYMMDD'))


To use the default date format of your application.


Hope this helps


regards


Marco



its_anandrjs

Hi,

Try this expression Date(Date#(Right(DateText,6),'YYMMDD'),'MM/DD/YYYY') as NewDate

Ex:-

LOAD Date(Date#(Right(DateText,6),'YYMMDD'),'MM/DD/YYYY') as NewDate,*;

LOAD * Inline

[
DateText
1150115

];



Regards

Anand

Not applicable
Author

Thanks everyone.  I am new to scripting and am still struggling with getting this to work.  Here is my script:

//-------- Start Multiple Select Statements ------
SKFVAB01.AMFLIBJ.MOMAST:
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;

MDATE and ODUDT are the two fields I am trying to convert to a date format.  How do I put the Date# function into this script?

Thanks again for your help!

kp

PrashantSangle

Hi,

Try like

SKFVAB01.AMFLIBJ.MOMAST:
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;

final_SKFVAB01.AMFLIBJ.MOMAST:

load *,

Date(Date#(RIGHT(MDATE,6),'YYMMDD'),'MM/DD/YYYY' AS NEW_MDATE,          //Try other suggestion over here

Date(Date#(RIGHT(ODUDT,6),'YYMMDD'),'MM/DD/YYYY' AS NEW_ODUDT           //Try other suggestion over here
Resident SKFVAB01.AMFLIBJ.MOMAST;

DROP TABLE SKFVAB01.AMFLIBJ.MOMAST;

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
MarcoWedel

SKFVAB01.AMFLIBJ.MOMAST:

LOAD

     PartNo,

     MO#,

     Date(Date#(RIGHT(ActStart,6),'YYMMDD'),'MM/DD/YYYY' AS ActStart,

     Date(Date#(RIGHT(DueDate,6),'YYMMDD'),'MM/DD/YYYY' AS DueDate,

      CurOp,

     Qty,

     OrdStat,

     CrtRatio,

     ReleaseLot,

     NitLot,

      CurFacility;
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;

MarcoWedel

or

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;

hope this helps

regards

Marco

Not applicable
Author

So here's the script I ran:

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


And here's the error I am getting:

Capture.PNG

Any ideas?

PrashantSangle

Hi,

As you can see you are missing closing bracket.

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;


Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂