Convert Number to date format

[#1]

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

[MK_QSL]

can you load your apps with only dates?

[nilesh_gangurde]

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

OR

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

[giakoum]

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

[ashwanin]

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.