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

1 Solution

Accepted Solutions
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;

View solution in original post

23 Replies
paulyeo11
Master
Master
Author

my QVW

Anil_Babu_Samineni

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

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable

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

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

paulyeo11
Master
Master
Author

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

Hi Shiva

I get DATE not found.

Paul

prma7799
Master III
Master III

Convert [Due Date] into date format...


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


Or Please share some sample data...


paulyeo11
Master
Master
Author

Hi Tres

I get error too,

Enclosed my raw data.

Paul Yeo

qlikview979
Specialist
Specialist

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)