Converting hours into day and vice versa

[Anonymous]

Hi All,

I have a column called time,

0 days

0.5 days

0.5 days?

0.33 days?

1 day

1 day?

1.5 days

1.5 days?

1.33 days

2 days

2 days?

2 hrs

2.5 days

2.5 days?

3 days

3 days?

4 days

4 days?

4 hrs

I want to do the data cleansibng in the backend, that is get rid of " days ?" and Hrs but i want the hrs to be converted into day... all this has to be done in the back hand

Thanks

B

[vgutkovsky · Accepted solution]

Try this:

num#(left(time,index(time,' ')-1)) / if(substringcount(lower(time),'hrs')>0,24,1) as time_new

Regards,

Vlad

[#3]

Hi ,

Use the below script while loading data:

Results:

Load if(Index(Column,'hrs')>0,(trim(purgechar(Column,'hrs'))/24),trim(purgechar(Column,'days?'))) as Days

resident Data;

Hope this help,

Anosh

[Anonymous]

Splendid!!! Thank u  Vlad and Anosh  ... both are giving the same result!!!