Combine two calculations in to one field

[qlikfresh]

Hi Guys

I have two calculations in my load script 

if([TestMethod]<>'Method 1' and RESULT_STATUS='Authorised' , AUTHORISED_ON - DATE_SUBMITTED, Today() - DATE_SUBMITTED) as Age1

If([TestMethod]='Method 1' and RESULT_STATUS='Authorised' , AUTHORISED_ON - TEST_CREATED_ON, Today() - TEST_CREATED_ON) AS Age 2

Is it possible to merge Age1 and Age2 in to one field? I have tried a nested IF statement however it was not giving me the desired result

Thanks! 🙂 

[Or · Accepted solution]

Assuming only one of the two fields can have a value and the value is numeric, you can use alt():

Alt(Expression1, Expression2) as Age

With newer versions of Qlik Sense, you could use Coalesce(Expression1,Expression2)  if the values can be a string, but I think in this case it's numeric since it's an age...

[Or]

Alt() picks out the first non-null numeric value. It looks like in your case, the first expression always returns a numeric value, so the second one will never get picked up.

What exactly is the expected outcome here? Two lines, each with a different value for Age? 

[qlikfresh]

Hey, the expected outcome is to have it calculate Today()-Date_Submitted if not equal to Method1, but if equal to Method 1 Today()-CreatedOn

[Or · Accepted solution]

Then it seems like you just need to remove the else section from both of your if() statements. In that case, if the condition is not met the result will be null and Alt() won't pick it. However, this seems a bit confusing, because in that case, you could just use a single if() statement:

if(RESULT_STATUS='Authorised', if([TestMethod]<>'Method 1'  AUTHORISED_ON - DATE_SUBMITTED, Today() - DATE_SUBMITTED)) as Age1

[qlikfresh]

Thanks ! 🙂