Solved: Re: date format - Qlik Community

Report Inappropriate Content · ‎2015-03-04

hello comunity,

I have an excel file and which consists a date column(YRMON) with too many records.

In excel initially it is in text format(As i attached snapshot).Here my question is once i load the data into qlikview then I want to change the other format to date format(ex:'YYYY-DD') instead of change the format in excel(manually it may take much time).

Please help me out in this.

robert_mika · ‎2015-03-05

All of Your YRMON data are in Number format but some of them has been written as so look like text(left adjustment)

I have used formula in Excel ISTEXT() to check for format

This will work:

LOAD

if(YRMON>100000,date(makedate(left(YRMON,4), right(YRMON,2)),'YYYY-MM'),date(YRMON,'YYYY-MM')) as YRMON,

Projections,

Amount

The >100000 is more than enough to make sure that the return value is correct.

I would advise to see the data source to check why this has been formated/coming this way

View solution in original post

robert_mika · ‎2015-03-04

t1:

load * inline

[ Dat

201308

201309

201310

]

;

load Date(Date#(Dat,'YYYYMM'),'YYYY-MM') as Datek

resident t1;

drop table t1

Report Inappropriate Content · ‎2015-03-04

Hi robert thanks for your quik response.

If I create Inline table for entering the our own data it takes much time because there are too many records.

I want to change the data other format(which is already loaded into qlikview without performing any changes in excel) to date format.

I hope now you got my requirement what exactly am looking for.

Anonymous · ‎2015-03-04

Load inline here just an example. It could be just

load

...

Date(Date#(Dat,'YYYYMM'),'YYYY-MM') as Datek

...

from (or resident) ...

maxgro · ‎2015-03-04

load

date(makedate(left(YRMON,4), right(YRMON,2)),'YYYY-MM') as YRMON,

Projections,

Amount

from

yourexcelfile;

robert_mika · ‎2015-03-04

I do not have your data

This was just an example

You can use the same formula in your load statement

Directory;

LOAD

Date(Date#(YRMON,'YYYYMM'),'YYYY-MM') as Datek,

Projection,

Amount

FROM

Your path with file....

Report Inappropriate Content · ‎2015-03-05

Thankq Robert,

I've tried same thing as you suggested but no appropriate result.

Anymore ideas?

Anonymous · ‎2015-03-05

Hi Sattish,

Can you just post your sample data(rows),

So that we can work with your exact data .. as it is the only format issue . so your actual data will be helpful.

robert_mika · ‎2015-03-05

Not sure.

I have replicated your data and as you see it works.

Could you attached sample of your Excel file?

Directory;

LOAD

Date(Date#(YRMON,'YYYYMM'),'YYYY-MM') as YearMonth,

Projection,

Amount

FROM

Projections.xlsx

(ooxml, embedded labels, table is Sheet1);

Report Inappropriate Content · ‎2015-03-05

Hi Robert and balakumar

Now, I've attached my sample excel file in my original post.

Please have a look at.