Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Question in Multi-Row Formula

Mostafa_Anwar
8 - Asteroid

Hello All,

Attached is my dataset, I want to use use formula in Multi-Row Formula stating that in case of the the value beside PO = NO and the value beside Invoice = Yes OR the value beside PO = Yes and the value beside Invoice = No then replace value NO with Yes only and in case of the value beside PO = NO and the value beside Invoice = No no change will happen. 

So how to do this, i have tried several times but the result i got changed all values to be Yes.

 

Thanks in advance

18 REPLIES 18
Thableaus
17 - Castor
17 - Castor

Hi @Mostafa_Anwar

 

Does this work well for you? WF appended.

 

Cheers,

DavidP
17 - Castor
17 - Castor

It was a bit of a struggle, but I managed to do it with a multi-row formula, i think...

mult row 1.png

 

 

if [Row-1:Value]='PO' and uppercase([Value])='NO' and ([Row+1:Value]='Invoice' and uppercase([Row+2:Value])='YES' or [Row+3:Value]='Invoice' and uppercase([Row+4:Value])='YES') then 'YES'

 

elseif [Row-1:Value]='Invoice' and uppercase([Value])='NO' and (([Row-3:Value]='PO' and uppercase([Row-2:Value])='YES') or ([Row-5:Value]='PO' and uppercase([Row-4:Value])='YES')) then 'YES'

 

elseif [Row-1:Value]='Invoice' and uppercase([Value])='NO' and ([Row+1:Value]='PO' and uppercase([Row+2:Value])='YES' or [Row+3:Value]='PO' and uppercase([Row+4:Value])='YES') then 'YES'

 

elseif [Row-1:Value]='PO' and uppercase([Value])='NO' and (([Row-3:Value]='Invoice' and uppercase([Row-2:Value])='YES') or ([Row-5:Value]='Invoice' and uppercase([Row-4:Value])='YES')) then 'YES'

 

else [Value] endif

Mostafa_Anwar
8 - Asteroid

Thanks Thableaus, i have noticed that the value of Actual Attendee was NO and after WF become as Yes so can you check this pls as i want PO, Invoice values only to be changed. 

Thableaus
17 - Castor
17 - Castor

Hey @Mostafa_Anwar I'm sorry.

 

Here is final solution. WF appended.

 

Cheers,

Mostafa_Anwar
8 - Asteroid

Thanks Thableaus, i tried to apply WF but didn't work so How to apply your WF on this attached datasets 

In case of the the value beside PO = NO and the value beside Invoice (Post Event) = Yes OR the value beside PO = Yes and the value beside Invoice (Post Event) = No then replace value NO with Yes only and in case of the value beside PO = NO and the value beside Invoice (Post Event) = No no change will happen

Thableaus
17 - Castor
17 - Castor

Hi @Mostafa_Anwar

See solution attached. Don't forget to replace the path to your original dataset.

 

Cheers,

Mostafa_Anwar
8 - Asteroid

Thanks Thableaus, I have reviewed the output but it seems that the WF didn't work as per the attached screenshotUntitled.jpg

Thableaus
17 - Castor
17 - Castor

@Mostafa_Anwar

 

Sorry I forgot to put another condition on there.

 

Try this one. Now it's going to work!

 

Cheers,

Mostafa_Anwar
8 - Asteroid

Many Thanks Thableaus, Truly appreciated.... The provided WF make my life easy now :) and bring me 

 

Last help please, Sorry i forgot to highlight this before, Is it possible to add another condition in the provided WF to be as per details below:

In case of Value called Approval :NA then the Value beside  Approval to be change from NO to be NA

Also In case of Value called Approval :NA then the Value beside  Approval to be change from NO to be NA

Attached is the modified dataset.

 

Many thanks in advance,   

 

Labels