Weekly challenge 286 - My solution is total is correct but few individual line items vary

[SandeepBabuS]

Hi Team, 

I have tried to solve the weekly challenge 286 - My solution is total is correct but a few individual line items vary.

Instead of using the Find Replace tool, I have used the Formula tool. The final answer is correct, but a few individual line items did not match.

Please attached files. One file belongs to me and the other one is the community solution file.

Please assist me.

[acarter881]

I'm not sure what you mean by a difference between your output and the solution.

Do you have the Expect Equals CReW macro? I attached it to this post.

See spoiler (Expect Equals does not return a difference between the solution and your output).

Spoiler

[SandeepBabuS]

Hi,

Thank you for the reply.

I apologize for the incorrect solution file provided in the initial post. Regret the inconvenience caused to you.

Please find the attached solution files.

Operative	Count	Operative (My Sol))	Count (My Sol)	Count (T/F)
Raccoon	114	Raccoon	114	TRUE
Snake	113	Snake	112	FALSE
Rat	45	Rat	46	FALSE
Cat	22	Cat	23	FALSE
Possum	16	Possum	16	TRUE
Beaver	15	Beaver	15	TRUE
Jellyfish	12	Jellyfish	12	TRUE
Monkey	11	Monkey	11	TRUE
Eagle	8	Eagle	7	FALSE
Bat	6	Bat	6	TRUE
Bobcat	5	Bobcat	5	TRUE
Elephant	4	Elephant	4	TRUE
Slug	4	Slug	4	TRUE
Frog	3	Frog	3	TRUE
Lizard	3	Lizard	3	TRUE
Mouse	3	Mouse	3	TRUE
Cow	2	Cow	1	FALSE
Deer	2	Deer	3	FALSE
Fox	2	Fox	2	TRUE
Gopher	2	Gopher	2	TRUE
Leopard	2	Leopard	2	TRUE
Caterpillar	1	Caterpillar	1	TRUE
Chicken	1	Chicken	1	TRUE
Insects	1	Insects	1	TRUE
Microbes	1	Microbes	1	TRUE
Moose	1	Moose	1	TRUE
Mountain Lion	1	Mountain Lion	1	TRUE
Octopus	1	Octopus	1	TRUE
Shark	1	Shark	1	TRUE
Total	402	Total	402

Total FALSE	6
Total TRUE	23

Note: Please refer to Challenge# 286.

Please assist.

[danilang · Accepted solution]

Hi @SandeepBabuS 

The difference comes down to the way the replacement was done.  The Alteryx solution replaces the Operative field only for those where it is originally "Animal".  Your solution performs the replacement in all the rows.  As an example of the difference in row 59, the Operator field is not "Animal" so the Alteryx solution doesn't change it.  

Your solution changes it to Deer because your IF statement looks like this

...
ELSEIF

(Contains([Article Title],'Deer', 1) OR
Contains([Operative],'Deer', 1))
THEN "Deer"
ELSEIF


(Contains([Article Title],'Eagle', 1) OR
Contains([Operative],'Eagle', 1))
THEN "Eagle"
ELSEIF

...

When processing line 59, the first clause that is true is the "Deer" one.  Your output field gets set to "Deer" and the statement moves on to the next row.  If you had put the "Eagle" clause before the "Deer" clause in your statement, that's the one that would have been used, setting the output field to "Eagle".

The totals match because there are 402 rows in the input and both of the solutions report on all the rows. 

Neither solution can be said to be wrong, because row 59 is ambiguous.  Does the phrase "deer with wings" refer to an eagle, or a deer that fell from a high place.   

Dan 

[SandeepBabuS · Accepted solution]

Thank you so much Dan. I have figured out the logical error in my expression. I have changed my expression (Expression: Animal5) in my workflow (Attached).   Also please check the tested data in the attached excel file and let me know your opinion.

Expression: Animal4 is the earlier one. 

Expression: Animal5 is the new one and it seems to be correct and to produce the correct output.

Thanks.

Sandeep Babu S

[KarlHood]

done