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

Multi Row Formula If Then statement returns 0

AlterID
5 - Atom

Good evening

 

I'm trying to get a string return into Field 8 instead of zero.  


Any thoughts, please?  Thanks!

Multi Row Info.PNGWorkflow pic.PNGInput.PNG

7 REPLIES 7
cmcclellan
13 - Pulsar

I don't understand the logic, do you really need a multi-row formula ?

AlterID
5 - Atom

Thanks so much for the response @

 

This is a simplified version of my question. 

 

When the data is missing, there are two null fields prior to a " symbol.  Normally there is only 1 null field prior.  Usually only 1 occurrence of this in 2000+ records but when it occurs, my fields are thrown off course

cmcclellan
13 - Pulsar

So something like this ?

 

IF isnull([Row-2:test]) and isnull([Row-1:test]) and [test] = '"'
THEN  "Missing" 
ELSE [test] ENDIF

2018-06-09 22_20_07-Alteryx Designer x64 - New Workflow2.yxmd_.png

AlterID
5 - Atom

Still 0.  Does the field type = V_string make a difference?

cmcclellan
13 - Pulsar

V_String just means that it's text - and it needs to be in it's stored a "

 

Do you understand what my code does ? It basically says if you get a null, then another null, then a " then change the " to be "Missing".

 

It won't change the earlier records which is just one null then a "

 

Should your logic really be looking at prior records to determine the value ?

Is the data in the spreadsheet you attached the exact same as the file you're wanting to process ?

 

Can you create another sheet in the XLS file to show the results that you're after ? (& possibly explain the logic on each row?)

 

Cheers :)

AlterID
5 - Atom

It did work.  Many thanks!

 

What's puzzling to me is why my If-then statement returned 0 while yours returned the correct string.  And why was 0 in field 10 and not Row - 2?  Although removing the Row - 2 still yields 0

 

Again, many thanks!

cmcclellan
13 - Pulsar

So why did mine work and yours didn't ? Because my "then" was just returning a value but yours was "[Row-2]=missing" - I've never seen that and I don't think it works like that.

 

I was still confused by your logic though, but I think you mean that you want "Missing" on row 8 when row 8 is null, row 9 is null and row 10 is "

 

in that case you should use this:

 

IF isnull([test]) and isnull([Row+1:test]) and [Row+2:test] = '"'
THEN  "Missing" 
ELSE [test] ENDIF

See the difference ? 

 

This logic is saying "this row is null, the next row is null and the row after is "  ... then make this row Missing"

 

The logic appears almost the same, but using Row PLUS rather than Row MINUS means we can use rows we haven't got to yet and put the "Missing" result in row 8

Labels