Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ravikumar_iyana
Contributor

converting date field DD TO MM

Hi,

I want to convert MONTH field -> DATE field

  1. Based on date field DD/MM/YYYY(presentDATA)-> YYMM (requiredDATA).
  2. after completing converstion not required to repet .

* NOTE: Converting DD into MM

DD/MM/YYYY(PRESENTDATA)YYMM(REQUIREDDATA)
01/01/20161601
02/01/20161602
03/01/20161603
01/02/2016

02/02/2016

03/02/2016

I'm tried like this

DATE(DATEFIELD,'DD/MM/YYYY') INTO DATE(DATEFIELD,'YYMM')

its not working.

Thanks,

Ravi

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: converting date field DD TO MM

table:

load Distinct date(convertedDate,'YYMM') as DateFinal;

load Distinct date(Date#(DateField,'MM/DD/YYYY'),'DD/MM/YYYY') as convertedDate;

load date(Date#(MyDate,'DD/MM/YYYY')) as DateField inline [

MyDate

01/01/2016

02/01/2016

03/01/2016

01/02/2016

02/02/2016

03/02/2016

04/02/2016

];

NoConcatenate

Final:

load DateFinal where peek=0;

load if(previous(text(DateFinal))=text(DateFinal),1,0) as peek, DateFinal;

load   DateFinal Resident table Order by DateFinal;

Drop Table table;

Result:

Capture.PNG

5 Replies
pradosh_thakur
Valued Contributor III

Re: converting date field DD TO MM

MID(date(DATE#(DATEFIELD,'DD/MM/YYYY'),'YYYYMM'),2)  AS datefield_new

or

if already on date format


MID(date(DATEFIELD,'YYMM') ,2) AS datefield_new

krishna_2644
Valued Contributor III

Re: converting date field DD TO MM

Try this : right(year(Date),2) & date(date#( Date ,'MM/DD/YYYY'),'MM') as YYMM;



t:

load *,right(year(Date),2) & date(date#( Date ,'MM/DD/YYYY'),'MM') as YY;

load * inline [

Date

01/01/2016

02/01/2016

03/01/2016

01/02/2016

02/02/2016

03/02/2016

];


1.PNG

ravikumar_iyana
Contributor

Re: converting date field DD TO MM

Hi Krishna,

my requirement is in FIELD: YY dont want to repate

ex:

yy:

1601

1602

1603

krishna_2644
Valued Contributor III

Re: converting date field DD TO MM

on the front end? what chart you want?

you have non distinct dates in the dates hence you are seeing the YYMM multiple time for every value of Date.

OmarBenSalem
Esteemed Contributor

Re: converting date field DD TO MM

table:

load Distinct date(convertedDate,'YYMM') as DateFinal;

load Distinct date(Date#(DateField,'MM/DD/YYYY'),'DD/MM/YYYY') as convertedDate;

load date(Date#(MyDate,'DD/MM/YYYY')) as DateField inline [

MyDate

01/01/2016

02/01/2016

03/01/2016

01/02/2016

02/02/2016

03/02/2016

04/02/2016

];

NoConcatenate

Final:

load DateFinal where peek=0;

load if(previous(text(DateFinal))=text(DateFinal),1,0) as peek, DateFinal;

load   DateFinal Resident table Order by DateFinal;

Drop Table table;

Result:

Capture.PNG

Community Browser