topic Re: CReate Date from a field in QlikView

CReate Date from a field

akpofureenughwu — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: CReate Date from a field

sunny_talwar — Mon, 25 Apr 2016 10:48:12 GMT

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;

Re: CReate Date from a field

sunny_talwar — Mon, 25 Apr 2016 10:51:22 GMT

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

Re: CReate Date from a field

akpofureenughwu — Mon, 25 Apr 2016 11:20:00 GMT

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

Re: CReate Date from a field

sunny_talwar — Mon, 25 Apr 2016 15:10:51 GMT

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

Re: CReate Date from a field

akpofureenughwu — Mon, 25 Apr 2016 15:25:27 GMT

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.

Re: CReate Date from a field

sunny_talwar — Mon, 25 Apr 2016 15:33:15 GMT

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

Re: CReate Date from a field

akpofureenughwu — Mon, 25 Apr 2016 15:55:15 GMT

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

Re: CReate Date from a field

akpofureenughwu — Mon, 25 Apr 2016 16:11:45 GMT

This is a script

Re: CReate Date from a field

sunny_talwar — Mon, 25 Apr 2016 16:30:16 GMT

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

Re: CReate Date from a field

akpofureenughwu — Mon, 25 Apr 2016 17:28:22 GMT

It didn't work

Re: CReate Date from a field

sunny_talwar — Mon, 25 Apr 2016 17:40:46 GMT

What error did you get?

Re: CReate Date from a field

agni_gold — Mon, 25 Apr 2016 18:51:59 GMT

try your fields like

Date(Date#(Due_Date,'YYYYMMDD'),'DD-MM-YYYY') AS Due_Date,

Month(Date(Date#(Due_Date,'YYYYMMDD'),'DD-MM-YYYY') ) AS Month,

Year(Date(Date#(Due_Date,'YYYYMMDD'),'DD-MM-YYYY') ) AS Year,

Quarter(Date(Date#(Due_Date,'YYYYMMDD'),'DD-MM-YYYY') ) AS Quarter

Re: CReate Date from a field

ajsjoshua — Mon, 02 May 2016 04:57:15 GMT

Hi,

I think field not found error is occuring.

Re: CReate Date from a field

dinuwanbr — Mon, 02 May 2016 05:18:48 GMT

Hi Akpofure,

PFA

Rgds,

Dinu1

Re: CReate Date from a field

Mon, 02 May 2016 06:15:10 GMT

Is the format is same across the file, meaning to say, first it is year, then month & last it is date as I can seen in the excel file attached

Regards,

Anand

Re: CReate Date from a field

sunny_talwar — Mon, 02 May 2016 06:26:04 GMT

Would you be able to point out where that error would be in the above script? I mean which field is not found?

Re: CReate Date from a field

ajsjoshua — Mon, 02 May 2016 06:30:20 GMT

Hi,

the Field < DueDate > is not found.

Re: CReate Date from a field

sunny_talwar — Mon, 02 May 2016 06:34:08 GMT

But I am not even using that in my script here: Re: CReate Date from a field

Re: CReate Date from a field

ajsjoshua — Mon, 02 May 2016 06:36:47 GMT

Hi Sunny,

PFA