IF or CASE Statement

[Former Member]

I have a Calculated Column I am trying to create in a Calculation View where I am using an IF Statement to calculate Day Buckets based on a Decimal Column called DAYS with the Syntax below;

if("DAYS"<1.5,'Less than 1 day',

if("DAYS"<3.5,'Less than 3 days',

if("DAYS"<5.5,'Less than 5 days',

if("DAYS"<7.5,'Less than 7 days',

if("DAYS">=7.5,'> 7 days',

'No Info Provided')))))

Currently this logic is only returning results as '> 7 days', and the DAYS Column definitely has numbers below 7.5 in my data, so all of the other Text Fields should be getting populated with results.

Is there any reason why this would be the case? Would I need to change the logic to a CASE Statement instead, or convert the DAYS Column from a Decimal to something else?

[former_member200930]

Hi,

Please use casting for day field. Cast it into INT.  please check below example.

if((int("DAY")<1.5,'Less than 1 day'

.

.

.

.

.

.

.

'No info Provided')

I hope, this will help you.

-Vikram Divekar

[Former Member]

Hi Andrew,

There could be a possibility that the column "DAYS" might be getting aggregated.

• Change the DAYS to be attribute(may be like Varchar 10)
• In Calculated column, type cast the DAYS field

if(decfloat("DAYS")<1.5,'Less than 1 day',

if(decfloat("DAYS")<3.5,'Less than 3 days',

if(decfloat("DAYS")<5.5,'Less than 5 days',

if(decfloat("DAYS")<7.5,'Less than 7 days',

if(decfloat("DAYS")>=7.5,'> 7 days',

'No Info Provided')))))

[Former Member]

Hi Andrew,

IF Statement provided by you works fine. Here are the screen shots.

Input:

Output:

[pfefferf]

Hi Andrew,

a CASE would not fit your requirement, because the CASE function supports only a simple case at the moment (only a = comparsion possible).

I tested your IF on a SPS10 system and it works w/o problems.

Did you check maybe the result with the data preview function (which limits the recoreds) which returned only records fitting to the "> 7 days" category?

Regards,

Florian