Solved: How to create a YearMonth from date field ? - 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

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;

View solution in original post

paulyeo11 · ‎2017-05-18

my QVW

Anil_Babu_Samineni · ‎2017-05-18

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

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2017-05-18

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

tresesco · ‎2017-05-18

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

paulyeo11 · ‎2017-05-18

Hi Anil

I try :-

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

I get error DATE not found.

I need load script.

Paul Yeo

paulyeo11 · ‎2017-05-18

Hi Shiva

I get DATE not found.

Paul

prma7799 · ‎2017-05-18

Convert [Due Date] into date format...

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

Or Please share some sample data...

paulyeo11 · ‎2017-05-18

Hi Tres

I get error too,

Enclosed my raw data.

Paul Yeo

qlikview979 · ‎2017-05-18

Hi,

PFA

I have Changed in your QV file Please check it.

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

or

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