date format

[#1]

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]

t1:

load * inline

[ Dat

201308

201309

201310

]

;

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

resident t1;

drop table t1

[#3]

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]

Load inline here just an example. It could be just

load

...

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

...

from (or resident) ...

[maxgro]

load

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

     Projections,

     Amount

from

     yourexcelfile;

[robert_mika]

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

[#7]

Thankq Robert,

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

Anymore ideas?

[Anonymous]

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]

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

[#10]

Hi Robert and balakumar

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

Please have a look at.