topic Re: How to Convert Number Format Date value in Date format value in QlikView

How to Convert Number Format Date value in Date format value

Mon, 13 Oct 2014 12:21:10 GMT

we picked Date in number format in Qlik,but I want to create Year and Month field from this Date value.

Example-->

I have date in (201402,201408,201409,201306,201305,201208) this format.

it is numerb format.

I tried all possibel value but I'm not able to get Year and Month from ablve value.

Please provide sol or formula for this

Re: How to Convert Number Format Date value in Date format value

mdmukramali — Mon, 13 Oct 2014 12:27:43 GMT

Dear,

can you try something like below,

LOAD *,Left(Data,4) as Year,

Right(Data,2) as Month

Inline

[

Data

201402

201408

201409

201306

201305

201208

]

;

Thanks,

Mukram

Re: How to Convert Number Format Date value in Date format value

Mon, 13 Oct 2014 12:29:38 GMT

I did not understand your question. Is this what you need?

=left(201402,4)=> 2014

= Right(201402,2) = 02

Re: How to Convert Number Format Date value in Date format value

ashfaq_haseeb — Mon, 13 Oct 2014 12:30:41 GMT

Try like this

=Month(MakeDate(left(201402,4),right(201402,2),1)) as Month

=Year(MakeDate(left(201402,4),right(201402,2),1)) as Year

Regards

ASHFAQ

Re: How to Convert Number Format Date value in Date format value

MarcoWedel — Mon, 13 Oct 2014 22:47:28 GMT

Hi,

one possible solution could be also:

LOAD *,
    Month(MonthName) as Month,
    Year(MonthName) as Year,
    QuarterName(MonthName) as QuarterName;
LOAD Date#(Date, 'YYYYMM') as MonthName
INLINE [
    Date
    201402
    201408
    201409
    201306
    201305
    201208
];

hope this helps

regards

Marco

Re: How to Convert Number Format Date value in Date format value

Tue, 14 Oct 2014 04:13:10 GMT

Hiii Ganesh,

First interpret this number format into Date format like this:-

Date#(Date,'YYYYMM') as Date

and then perform any operation with that date like:

year(Date) as Year,

month(Date) as Month

Complete Script is like:-

Load year(Date) as Year,month(Date) as Month;

Load Date#(Date,'YYYYMM') as Date;

Thanks & Regards

Prince Anand