Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

How to create a YearMonth from date field ?

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

23 Replies
tresesco
MVP
MVP

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
Master III
Master III

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
Master III
Master III

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
Master
Master
Author

Hi P M

Thank you your code work fine.

Also the rest , thank you for your help.

Paul

paulyeo11
Master
Master
Author

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

Try like:

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

qlikview979
Specialist
Specialist

Hi Try this

prma7799
Master III
Master III

Try this

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

paulyeo11
Master
Master
Author

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

Apply this in script

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