topic Re: Reformat of dates in load script in QlikView

Reformat of dates in load script

Tue, 27 Jan 2015 02:42:09 GMT

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?

Re: Reformat of dates in load script

prabhuappu — Tue, 27 Jan 2015 05:03:05 GMT

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

Re: Reformat of dates in load script

MarcoWedel — Tue, 27 Jan 2015 06:52:36 GMT

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

To use the default date format of your application.

Hope this helps

regards

Marco

Re: Reformat of dates in load script

its_anandrjs — Tue, 27 Jan 2015 06:59:44 GMT

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

Re: Reformat of dates in load script

Tue, 27 Jan 2015 12:32:21 GMT

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!

Re: Reformat of dates in load script

PrashantSangle — Tue, 27 Jan 2015 12:45:06 GMT

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

Re: Reformat of dates in load script

MarcoWedel — Tue, 27 Jan 2015 13:27:15 GMT

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;

Re: Reformat of dates in load script

MarcoWedel — Tue, 27 Jan 2015 13:29:09 GMT

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

Re: Reformat of dates in load script

Tue, 27 Jan 2015 13:44:54 GMT

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?

Re: Reformat of dates in load script

PrashantSangle — Tue, 27 Jan 2015 15:05:13 GMT

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

Re: Reformat of dates in load script

Tue, 27 Jan 2015 15:49:52 GMT

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

Re: Reformat of dates in load script

PrashantSangle — Tue, 27 Jan 2015 15:59:48 GMT

Hi,

Are you sure do you a field name ActStart in table

Is it ActStart or MDate??

Regards

Re: Reformat of dates in load script

Tue, 27 Jan 2015 16:48:03 GMT

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

Re: Reformat of dates in load script

Anonymous — Tue, 27 Jan 2015 16:54:58 GMT

Is it Oracle? Try:

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

Re: Reformat of dates in load script

Tue, 27 Jan 2015 17:03:30 GMT

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.

Re: Reformat of dates in load script

Anonymous — Tue, 27 Jan 2015 17:09:40 GMT

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;

Re: Reformat of dates in load script

Tue, 27 Jan 2015 20:17:01 GMT

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!

Re: Reformat of dates in load script

Anonymous — Tue, 27 Jan 2015 20:37:26 GMT

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