Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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

Highlighted

Or


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


To use the default date format of your application.


Hope this helps


regards


Marco



Highlighted

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

Highlighted
Not applicable

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

Highlighted

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

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;

Highlighted

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

Highlighted
Not applicable

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?

Highlighted

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.