Multiple Condition of Data addition and minus

[SH_94]

Hi Community,

I would like to ask how we can build the workflow so that it able produce the result as per screenshot below. I have few condition in order to achieve the final result:

1. I would like to join the data A and data B before performing data addition or data minus.

2. Once it is combined, i have the criteria as below:

   - If type is A, the amount from data A will be add together with amount from data B

   - If type is B and C, the amount from data A will minus the amount from data B

   - If type is D , the amount from data A will be add together with the amount from data B

I know how to build the step 1 in this case, but i not sure how to build the workflow for the second step.I also included the excel file below for your reference

[djaipras]

You can use the Formula Tool to create a nested if statement.

Example:

IF ... THEN ...

ELSEIF ... THEN ...

ELSEIF ... THEN ...

ELSE ...

ENDIF

Attached is an example of a workflow that I think does what you're looking for, but some assumptions: 

1. It assumes that the only pieces of information needed from Data B is the Customer and the Amount

2. It assumes the Customer number is used for the join

3. It assumes that Data A and Data B are added as separate inputs

[AngelosPachis]

Hi @SH_94 ,

Based on the desired output you provided, I think you got the question the other way around, so if your Type is A or D then you want to subtract, else you want to add. Anyway you can change it as needed, I chose to do that with a formula tool and an If statement.

Hope that helps, let me know if that worked for you.

Cheers,

Angelos

[SH_94]

Hi @AngelosPachis ,

Thank you for the prompt response.

Can i clarify with you on the following ?

1. If now i would like to put the type D under the same category with type B and C, are we still using the formula as below?

IF [Type] IN("A") THEN
[Amount] - [Amount Data B]

ELSEIF [Type] IN("B","C","D") THEN 

[Amount] +[Amount Data B]

[SH_94]

Hi @djaipras ,

May i know why you choose to use upper case in this case? Normally in what circumstances that we can use this formula?

Thank you.

[AngelosPachis]

That is correct @SH_94 , or for simplicity you can do 

IF [Type]="A" THEN

[Amount] - [Amount Data B]

ELSEIF [Type] IN("B","C","D") THEN 

[Amount] +[Amount Data B]

ENDIF

The In function makes more sense if you have more than one values to check against, that's why I suggested the "=" sign, but the In function would work equally.

[djaipras]

I do it when I am assuming that the field can be manually entered and I want to ignore case sensitivity. 

However, I just checked this further, and it seems Alteryx ignores case sensitivity when comparing strings with "=", which in my programming/DB experience isn't always a default. So it's me being overly cautious, and you can most likely remove the UpperCase function for your purpose.

[SH_94]

Hi @AngelosPachis ,

Thanks a lot for the clarification. I have encounter another set of data which as shown in the screenshot below:

Previously, type A is one category and type B,C and D are another category.

However, there are further breakdown on the respective type category and the criteria will be as follow:

1. If the type is A, we need to see whether the subtype is AA or AB. If it is AA, we need to minus the data Set A against data Set B. if the subtype is AB, we just need to add the data Set A against data Set B.

2. If type B and D , we not need to look at subtype since it is only one subtype for data B and D. Hence,we just need to minus the data Set A against data Set B for type B and D.

3. If the type is C, we need to see whether the subtype is CC or CF. If it is DF, we need to minus the data Set A against data Set B. if the subtype is CC, we just need to add the data Set A against data Set B.

It is a bit complicated and would like to know how you will build the workflow in this case?

[AngelosPachis · Accepted solution]

Hi @SH_94 ,

As you can see for yourself, when you typed the question you used the word IF many times, so again you are looking for an IF expression.

That probably would be:

IF [Type]="A" THEN 
//1.
     IF [Subtype]="AA" THEN [Amount]-[Amount Data B]
     ELSEIF [Subtype]="AB" THEN [Amount]+[Amount Data B]
     ELSE NULL()
     ENDIF
ELSEIF [Type]="C" THEN 
//3.
     IF [Subtype]="DF" THEN [Amount]-[Amount Data B]
     ELSEIF [Subtype]="CC" THEN [Amount]+[Amount Data B]
     ELSE NULL()
     ENDIF
ELSE
//2.
    [Amount]-[Amount Data B]
ENDIF

Can you test that and let me know if that worked for you please and if you have any questions about the logic behind it?

Thanks,

Angelos

[SH_94]

Hi @AngelosPachis ,

Thanks a lot for your explanation.

Currently i testing the logic and would like to know about the symbol and meaning behind it for the following:

1. May i know what is the purpose of we put //1,//3 and //2?

2. May i know why we would need to use ELSE Null () in this case? Normally in what cases that you will use this formula?

3. May i know when we need to use ELSEIF and ELSE? Is it we will use ELSE before the last if statement and ELSEIF will be used in the middle of logic statement?

Thank you again for your help.