Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

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
Honored Contributor III

Re: How to create a YearMonth from date field ?

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;

23 Replies
paulyeo11
Valued Contributor II

Re: How to create a YearMonth from date field ?

my QVW

Re: How to create a YearMonth from date field ?

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

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

laddu_927
Valued Contributor

Re: How to create a YearMonth from date field ?

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

MVP
MVP

Re: How to create a YearMonth from date field ?

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

paulyeo11
Valued Contributor II

Re: How to create a YearMonth from date field ?

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
Valued Contributor II

Re: How to create a YearMonth from date field ?

Hi Shiva

I get DATE not found.

Paul

prma7799
Honored Contributor III

Re: How to create a YearMonth from date field ?

Convert [Due Date] into date format...


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


Or Please share some sample data...


paulyeo11
Valued Contributor II

Re: How to create a YearMonth from date field ?

Hi Tres

I get error too,

Enclosed my raw data.

Paul Yeo

qlikview979
Valued Contributor

Re: How to create a YearMonth from date field ?

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)