topic Re: date format in QlikView

date format

Wed, 04 Mar 2015 21:57:34 GMT

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.

Re: date format

robert_mika — Wed, 04 Mar 2015 22:05:15 GMT

t1:

load * inline

[ Dat

201308

201309

201310

]

;

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

resident t1;

drop table t1

Re: date format

Wed, 04 Mar 2015 22:32:05 GMT

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.

Re: date format

Anonymous — Wed, 04 Mar 2015 22:39:08 GMT

Load inline here just an example. It could be just

load

...

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

...

from (or resident) ...

Re: date format

maxgro — Wed, 04 Mar 2015 22:41:45 GMT

load

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

Projections,

Amount

from

yourexcelfile;

Re: date format

robert_mika — Wed, 04 Mar 2015 22:43:29 GMT

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

Re: date format

Thu, 05 Mar 2015 05:37:02 GMT

Thankq Robert,

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

Anymore ideas?

Re: date format

Anonymous — Thu, 05 Mar 2015 05:44:30 GMT

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.

Re: date format

robert_mika — Thu, 05 Mar 2015 05:50:49 GMT

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

Re: date format

Thu, 05 Mar 2015 06:01:33 GMT

Hi Robert and balakumar

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

Please have a look at.

Re: date format

robert_mika — Thu, 05 Mar 2015 06:29:48 GMT

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