Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Or
Date(Date#(Mid(datefield,2), 'YYMMDD'))
To use the default date format of your application.
Hope this helps
regards
Marco
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
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
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
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;
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
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:
Any ideas?
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