topic Re: How to create a YearMonth from date field ? in QlikView

How to create a YearMonth from date field ?

paulyeo11 — Thu, 18 May 2017 06:27:23 GMT

Hi All

I have below script , i need to create a field name YearMonth from DATE.

Hope some one can advise me. I keep get error msg. when i try partial reload.

sales:

LOAD Subject,

[Due Date] as DATE,

[Item Name],

Quantity,

[List Price],

[Item Comment],

Discount,

Description

FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

I have

Re: How to create a YearMonth from date field ?

paulyeo11 — Thu, 18 May 2017 06:28:14 GMT

my QVW

Re: How to create a YearMonth from date field ?

Anil_Babu_Samineni — Thu, 18 May 2017 06:33:06 GMT

May be this? I assume your date format is DD-MM-YYYY

=SubField(DATE,'-',2) & '-' & Right(DATE,4)

Re: How to create a YearMonth from date field ?

Anonymous — Thu, 18 May 2017 06:34:07 GMT

wil this do??

sales:

Load *, Date(Date#(Date,'MM-DD-YYYY'),'YYYY-MMM') as YearDate;

LOAD Subject,

[Due Date] as DATE,

[Item Name],

Quantity,

[List Price],

[Item Comment],

Discount,

Description

FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

Re: How to create a YearMonth from date field ?

tresB — Thu, 18 May 2017 06:37:13 GMT

MonthName(Date#(DATE,'DD-MM-YYY')) as MonthYear

Re: How to create a YearMonth from date field ?

paulyeo11 — Thu, 18 May 2017 06:37:27 GMT

Hi Anil

I try :-

SubField(DATE,'-',2) & '-' & Right(DATE,4) as Date,

I get error DATE not found.

I need load script.

Paul Yeo

Re: How to create a YearMonth from date field ?

paulyeo11 — Thu, 18 May 2017 06:39:11 GMT

Hi Shiva

I get DATE not found.

Paul

Re: How to create a YearMonth from date field ?

prma7799 — Thu, 18 May 2017 06:39:33 GMT

Convert [Due Date] into date format...

=Date(Date#(DATE,'DD-MM-YYY'),'DD-MM-YYYY')

Or Please share some sample data...

Re: How to create a YearMonth from date field ?

paulyeo11 — Thu, 18 May 2017 06:42:04 GMT

Hi Tres

I get error too,

Enclosed my raw data.

Paul Yeo

Re: How to create a YearMonth from date field ?

qlikview979 — Thu, 18 May 2017 06:50:30 GMT

Hi,

PFA

I have Changed in your QV file Please check it.

=SubField(DATE,'-',3)&SubField(DATE,'-',2)

=SubField(DATE,'-',3)&'-'&SubField(DATE,'-',2)

Re: How to create a YearMonth from date field ?

tresB — Thu, 18 May 2017 06:50:47 GMT

If your field name is [Due Date], the expression becomes:

MonthName(Date#([Due Date],'DD-MM-YYYY')) as MonthYear

And yes, 'YYYY' not 'YYY'

Re: How to create a YearMonth from date field ?

prma7799 — Thu, 18 May 2017 06:51:00 GMT

Please check

Test:

LOAD * ,

MonthName(Date#([Due Date],'DD-MM-YYY')) as MonthYear,

(Date#([Due Date],'DD-MM-YYY')) as DueDate1;

LOAD Subject,

[Opportunity Name],

[Customer No],

[Sales Order Number],

[Quote Name],

[Purchase Order],

[Contact Name],

[Due Date],

Carrier,

Pending,

Status,

Adjustment,

[Sales Commission],

[Excise Duty],

Total,

[Sub Total],

[Tax Type],

[Discount Percent],

[Discount Amount],

[S&H Amount],

[Organization Name],

[Assigned To],

[Created Time],

[Modified Time],

Currency,

[Conversion Rate],

[Last Modified By],

[Pre Tax Total],

[Enable Recurring],

Frequency,

[Start Period],

[End Period],

[Payment Duration],

[Invoice Status],

[PO Date],

[Billing Address],

[Shipping Address],

[Billing PO Box],

[Shipping PO Box],

[Billing City],

[Shipping City],

[Billing State],

[Shipping State],

[Billing Postal Code],

[Shipping Postal Code],

[Billing Country],

[Shipping Country],

[Item Name],

Quantity,

[List Price],

[Item Comment],

Discount,

[Item Discount Percent],

Tax1,

Tax2,

Tax3,

[Terms & Conditions],

Description

FROM

Sales_Order.csv

(txt, utf8, embedded labels, delimiter is ',', msq);

Re: How to create a YearMonth from date field ?

prma7799 — Thu, 18 May 2017 06:52:24 GMT

Test:

LOAD Subject,

[Opportunity Name],

[Customer No],

[Sales Order Number],

[Quote Name],

[Purchase Order],

[Contact Name],

[Due Date],

Carrier,

Pending,

Status,

Adjustment,

[Sales Commission],

[Excise Duty],

Total,

[Sub Total],

[Tax Type],

[Discount Percent],

[Discount Amount],

[S&H Amount],

[Organization Name],

[Assigned To],

[Created Time],

[Modified Time],

Currency,

[Conversion Rate],

[Last Modified By],

[Pre Tax Total],

[Enable Recurring],

Frequency,

[Start Period],

[End Period],

[Payment Duration],

[Invoice Status],

[PO Date],

[Billing Address],

[Shipping Address],

[Billing PO Box],

[Shipping PO Box],

[Billing City],

[Shipping City],

[Billing State],

[Shipping State],

[Billing Postal Code],

[Shipping Postal Code],

[Billing Country],

[Shipping Country],

[Item Name],

Quantity,

[List Price],

[Item Comment],

Discount,

[Item Discount Percent],

Tax1,

Tax2,

Tax3,

[Terms & Conditions],

Description

FROM

Sales_Order.csv

(txt, utf8, embedded labels, delimiter is ',', msq);

Test2:

LOAD * ,

MonthName(Date#([Due Date],'DD-MM-YYY')) as MonthYear,

(Date#([Due Date],'DD-MM-YYY')) as DueDate1

Resident Test ;

DROP Table Test;

Re: How to create a YearMonth from date field ?

paulyeo11 — Thu, 18 May 2017 06:58:43 GMT

Hi P M

Thank you your code work fine.

Also the rest , thank you for your help.

Paul

Re: How to create a YearMonth from date field ?

paulyeo11 — Thu, 18 May 2017 07:05:42 GMT

Hi P M

i Try below 2 script :-

MonthName(Date#([Due Date],'YYY')) as Year,

year([Due Date]) as [Year_],

I cannot get the value for year.

Paul Yeo

Re: How to create a YearMonth from date field ?

tresB — Thu, 18 May 2017 07:08:13 GMT

Try like:

Year(Date#([Due Date],'DD-MM-YYYY')) as Year,

Re: How to create a YearMonth from date field ?

qlikview979 — Thu, 18 May 2017 07:08:30 GMT

Hi Try this

Re: How to create a YearMonth from date field ?

prma7799 — Thu, 18 May 2017 07:09:40 GMT

Try this

year((Date#([Due Date],'DD-MM-YYY'))) as [Year_]

Re: How to create a YearMonth from date field ?

paulyeo11 — Thu, 18 May 2017 07:25:12 GMT

Hi Tres

I try the value is blank.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +65 9326 1804

www.tdstech.com<http://www.tdstech.com>

Re: How to create a YearMonth from date field ?

qlikview979 — Thu, 18 May 2017 07:25:20 GMT

Apply this in script

Date(Date#([Due Date],'DD-MM-YYYY'),'YYYY-MM-DD') as DATE,