topic Re: Convert Number to date format in QlikView

Convert Number to date format

Tue, 28 Jan 2014 12:12:16 GMT

Hi All,

I Have one table Tab1 and in that i have many columns like CompName,Startdate,Enddate,CustCode,PNumber,Qty,LicenseTerm,CustCode,Status...

In that Status contains Acive, inactive, Expire. in my DB is status is Active then it's Start date and End date is in number format like 734806,734926,735921...

and for rest of all Status the date format is like '12/3/2012','11/2/2010'....

My requirement is i want to load Start date if Status is Active then like '12/3/2012' like as inactive, Expire statuses

i used following logic in scripting based on Qlikview community posts, but i'm getting errors

if(Status = 'Active',date(date#(99999999 - date_start,'MMDDYYYY')),Date(date_start)) as Date_start,

if(Status = 'Active',date(date#(99999999 - date_end,'MMDDYYYY')),Date(date_start)) as Date_end

And Custcode column loading some exponential values(3.91E+15) and some error values (02/21090300/01282) and , i can't understand why it's loading like that. How can we avoid these values?

Could you please somebody look into this issue and help me on this

Thanks,

M V

Re: Convert Number to date format

MK_QSL — Tue, 28 Jan 2014 12:13:48 GMT

can you load your apps with only dates?

Re: Convert Number to date format

nilesh_gangurde — Tue, 28 Jan 2014 12:15:49 GMT

Try this.

if(Status = 'Active',date(date#(date_start,'MM/DD/YYYY')),Date(date_start)) as Date_start,

if(Status = 'Active',date(date#(date_end,'MM/DD/YYYY')),Date(date_start)) as Date_end

you can use this

if(Status = 'Active',date(date_start,'MM/DD/YYYY'),Date(date_start)) as Date_start,

if(Status = 'Active',date(date_end,'MM/DD/YYYY'),Date(date_start)) as Date_end

-Nilesh

Re: Convert Number to date format

giakoum — Tue, 28 Jan 2014 12:19:54 GMT

These numbers, like 734806,734926,735921 correspond to dates in year 3912 and later. Are you sure there is nothing wrong with the data?

Re: Convert Number to date format

ashwanin — Tue, 28 Jan 2014 12:21:24 GMT

Hi,

What i understand from your query is that you have problem with the date format, not with the status .

try using the

date(date_start,'DD-MMM-YYYY') as Date_start &

date(date_end,'DD-MMM-YYYY') as Date_end

Because once it converted to date format then what ever the condition you have applied ie active or inactive, QV will reflect the correct date format.