CReate Date from a field

[akpofureenughwu]

Good day friends, I'm working on a job, I need to produce a chart with Month, Quarter and Year data but they were arranged in a table with this format: 20150413 meaning Year 2013 April 13Th. How do I convert format to produce Month, Quarter and Year.

[sunny_talwar]

Try this in the load script:

LOAD Year([Due Date]) as Year,

           Month([Due Date]) as Month,

           Day([Due Date]) as Day,

           [Due Date];

LOAD Date#([Due Date], 'YYYYMMDD') as [Due Date]

FROM Source;

[sunny_talwar]

For the sample attached, you can try this script:

Table:

LOAD Year([Due Date]) as Year,

          Month([Due Date]) as Month,

          Day([Due Date]) as Day,

          [Due Date];

LOAD Date#([Due Date], 'YYYYMMDD') as [Due Date]

FROM

[Due Date.xlsx]

(ooxml, embedded labels, table is Sheet1);

Some useful links to look at

Why don’t my dates work?

Get the Dates Right

[akpofureenughwu]

Thanks Sunny, But the original field is in a QVD file.

So how do I creat day, month, quarter and year from this field. When the data on the Due Date field is in the format of the excel file I sent earlier. Thanks in advance

[sunny_talwar]

Add this after the table name and see what you get:

LOAD *,

          Year([DUE DATE]) as Year,

          Month([DUE DATE]) as Month,

          Day([DUE DATE]) as Day;

LOAD *,

          Date#([DUE DATE], 'YYYYMMDD') as [DUE DATE];

LOAD .....

[akpofureenughwu]

Thanks for your assistance Sunny. After the table name, I added this

LOAD *,

          Year([DUE DATE]) as Year,

          Month([DUE DATE]) as Month,

          Day([DUE DATE]) as Day;

LOAD *,

          Date#([DUE DATE], 'YYYYMMDD') as [DUE DATE]; to the script

I didn't see as different... No Day, Month nor Year field was created.

[sunny_talwar]

Would you be able to share your complete script in a text file?

[akpofureenughwu]

I don't understand Sunny.. Should I send u the qvd file or this ...

[akpofureenughwu]

This is a script

[sunny_talwar]

Try this:

LOAD *,

          Year([DUE DATE]) as Year,

          Month([DUE DATE]) as Month,

          Day([DUE DATE]) as Day;

LOAD *,

          Date#([DUE DATE], 'YYYYMMDD') as [DUE DATE];

LOAD [CUSTOMER - CUSTOMER Level 01 (Key)],

     [CUSTOMER - CUSTOMER Level 01 (Text)],

     [Distribution Channel - Distribution Channel Level 01 (Key)],

     [Distribution Channel - Distribution Channel Level 01 (Text)],

     [Sales Organization - Sales Organization Level 01 (Key)],

     [Sales Organization - Sales Organization Level 01 (Text)],

     [Storage location - Storage location Level 01 (Key)],

     [Storage location - Storage location Level 01 (Text)],

     [VESSEL - VESSEL Level 01 (Key)],

     [VESSEL - VESSEL Level 01 (Text)],

     [CRUDE TYPE - CRUDE TYPE Level 01 (Key)],

     [CRUDE TYPE - CRUDE TYPE Level 01 (Text)],

     [PRODUCER - PRODUCER Level 01 (Key)],

     [PRODUCER - PRODUCER Level 01 (Text)],

     [BILLING DOC - BILLING DOC Level 01 (Key)],

     [BILLING DOC - BILLING DOC Level 01 (Text)],

     [B/L DATE - B/L DATE Level 01 (Key)],

     [B/L DATE - B/L DATE Level 01 (Text)],

     Amount,

     [Amount - Unit],

     [DUE DATE],

     [DUE DATE - Unit],

     [Net Value],

     [Net Value - Unit],

     [QTY (in BBL’s)],

     [QTY (in BBL’s) - Unit],

     [UNIT PRICE],

     [UNIT PRICE - Unit]

FROM

(qvd);