Solved: Re: How to create a YearMonth from date field ? - Page 2 - Qlik Community

paulyeo11 · ‎2017-05-18

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

tresesco · ‎2017-05-18

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

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

And yes, 'YYYY' not 'YYY'

prma7799 · ‎2017-05-18

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

prma7799 · ‎2017-05-18

Or

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;

paulyeo11 · ‎2017-05-18

Hi P M

Thank you your code work fine.

Also the rest , thank you for your help.

Paul

paulyeo11 · ‎2017-05-18

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

tresesco · ‎2017-05-18

Try like:

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

qlikview979 · ‎2017-05-18

Hi Try this

prma7799 · ‎2017-05-18

Try this

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

paulyeo11 · ‎2017-05-18

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>

qlikview979 · ‎2017-05-18

Apply this in script

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