Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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.
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.