If Formula in ARIS Query Spreadsheet

RUSHIKESH MANE on May 30, 2023

Hello Community!

I am trying to add nested IF formula in the Query to fullfill the below requirement

	A	B	C
1	Column 1	Column 2	Column3
2	1.00		Yes
3	1.00	1.00	Not Sure
4		1.00	No
5			default

I want Coulmn 3 to be yes, Not Sure, No, or Default based on these combinations of values ("1" and "null") in Column 1 and Column 2
(Column 1 and Column 2 values are coming by using the COUNT formula for Objects)

and for that I have written below nested IF Formula.

=IF(A2=="1.00";IF(B2=="1.00";"Not-Sure";"Yes");IF(B2=="1.00";"No";"default"))

But When I run the query I am getting Below error message

Could you please suggest me where am I doing logical/Syntactical error.
how can I modify the Formula to get required result out of the Query.

Thank you
Best regards,
Rushikesh

Notify Moderator

Best Reply

Klemens Hauk on June 10, 2023

Hi Rushikesh,

some time ago, but as nobody is answering I take some time to enter your query in ARIS. I can reproduce your problem. The formula calculation in the embedded Excel sheet does not work as expected if you are using references to more than one cell in your function. So the same problem you have if you write the formula =E2+F2. The general behaviour is that the first argument is replaced by a semicolon separated list of all cells in column E that are not empty and the same for F2. That leads to unexpected results or to Excel Syntax errors. This is for sure not what is expected. Maybe a specialist from SAG can comment this.

What I am normally doing is, to save the result of a query as an Excel table (not export!), if I need additional Excel functionality. Then I have the table as modell and can work with Excel as expected. For sure you have to repeat the procedure if starting a new query.

Regards

Klemens

or register to reply.

Show in context Notify Moderator

7 Replies

Veronika Ellermann on May 31, 2023

Hi Rushikesh,

I don't understand which value you want to display in which case. But I understand you have 4 options:

not sure
default
No
Yes

So, in my example, they correspond to the following numbers (and you don't need to put the numbers into quotation marks unless they are text)

not sure: 1
default: 2
No: 3
Yes: 4

=IF(D2==1;"not sure";IF(D2==2;"default";IF(D2==3;"No";"Yes")))

I am not sure if you want to add the possibility that a sum is created and that based on the result of the sum, the result is displayed. If that is the case, you can use the formula below:

=IF(SUM(C2:D2)==1;"not sure";IF(SUM(C2:D2)==2;"default";IF(SUM(C2:D2)==3;"No";"Yes")))

Hope this helps.

Best,

Veronika

Like

Sign in
or register to reply.

Notify Moderator
Klemens Hauk on May 31, 2023

Hello,

@Veronika: I think your solution is not what Rushikesh is aked for. The logic is not the same.

@Rushikesh: Your formula is syntactically and logically correct. The problem is, that a syntax error is reported that I also cannot understand. Each "if" function has three parameters, there are no missing brackets or similar. To be sure I tried the same formula, and it works in my environment (with the German "WENN", but this could not be the difference to English "if"). Sorry to ask, but are you really sure using exactly the formula you are showing in the posting?

Regards

Klemens

Like

Sign in
or register to reply.

Notify Moderator
RM

RUSHIKESH MANE on June 2, 2023

Hi @Klemens, @Veronika and Community,

Greetings

@Veronika: Thank you for your reply

@Klemens: Thank you for your reply and aslo cross checking it in your environment too.

FYI, Currently I am on ARIS Architect version: 10.0.21.0.3248782

as @Klemens asked I am providing the same formula but unfortunately it's not working fine in my environment.

and the result is

I have tried one alternative formula too.

=IF(AND(E2=="1.00";F2=="1.00");"Not-Sure";IF(AND(E2=="";F2=="1.00");"No";IF(AND(E2=="1.00";F2=="");"Yes";"Default")))

OR adding $ doesn't have any effect to the Formula/result.

=IF(AND($E2=="1.00";$F2=="1.00");"Not-Sure";IF(AND($E2=="";$F2=="1.00");"No";IF(AND($E2=="1.00";$F2=="");"Yes";"Default")))

This formula does not give an error but also not meeting the requirement.

Providing the Below template and Results using this formula

in the Template

and the Result is

This result is not coming for all the respective Row's coming only for on single row.

Any Suggestions/ feedbacks are most welcome

Thank you
Best Regards,
Rushikesh

Like

Sign in
or register to reply.

Notify Moderator
Klemens Hauk on June 2, 2023

Hi Rushikesh,

in the second screenshots where you are showing the results with the error message , part of the formula in G2 is shown in the edit line. There you can see "...if (F2;F3;F4..." and so on. The semicolon is the separator for the parameters, so you have a lot more than 3 parameters in your formula. I am not sure what has happened or what you try to reach. Normaly you write your formula in your case in G2 and copy it to G3, G4 and so on and the row is automatically changed.

My example:

Don`t be confused regarding the WENN. I am in a German environment.

Regards

Klemens

Like

Sign in
or register to reply.

Notify Moderator
RM

RUSHIKESH MANE on June 3, 2023

Hi @Klemens and Community!

Really Sorry! For stretching this Query little longer....

When I use formaula manually for all rows it's working perfectly fine in my environment too.

And As you rightly pointed out the part of the formula in G2 shown in the edit line "...if (F2;F3;F4..."
That is shown after the execution of the query

but to be even more precise
my sample Query looks like this

Where E2 and F2 are nothing but "=COUNT(C2)" and "=COUNT(D2)" respectively.
While running the Query,
FYI: Selected starting point as "Sales order processing (as-is)" EPC from UMG database.
And Suprisingly, while writing formula it doesn't give an error but after execution it does.

So, to repeat the requirement I need G5 to be "YES" and all other G2,G3,G4,G6, ..... to be "No" as per the same formula.
now I hope, I am very much clear with my requirement out of this query

I am doing some mistake but not getting what is that exactly
I would be Greatly thankful to you if you realized me with my mistake.

Best Regards,
Rushikesh

Like

Sign in
or register to reply.

Notify Moderator
Klemens Hauk on June 10, 2023

Best reply

Hi Rushikesh,

some time ago, but as nobody is answering I take some time to enter your query in ARIS. I can reproduce your problem. The formula calculation in the embedded Excel sheet does not work as expected if you are using references to more than one cell in your function. So the same problem you have if you write the formula =E2+F2. The general behaviour is that the first argument is replaced by a semicolon separated list of all cells in column E that are not empty and the same for F2. That leads to unexpected results or to Excel Syntax errors. This is for sure not what is expected. Maybe a specialist from SAG can comment this.

What I am normally doing is, to save the result of a query as an Excel table (not export!), if I need additional Excel functionality. Then I have the table as modell and can work with Excel as expected. For sure you have to repeat the procedure if starting a new query.

Regards

Klemens

Like

Sign in
or register to reply.

Notify Moderator
RM

RUSHIKESH MANE on June 13, 2023 as Reply to Klemens Hauk

Hi Klemens,

Thank you for your valuable time and help...

lets stay tuned for hearing from some specialist.

Thanks again Klemens.
Regards
Rushikesh

Like

Sign in
or register to reply.

Notify Moderator