Need a help to resolve issue get formula

[rojan]

Hi ,

can I get a help to resolve below issue. From below table i want to get data in such a manner. if column 2 is blank then I want to get data from 3 column other wise from column 1.

And I tried below formula but it does not work

IF ISNULL([COLUMN2])THEN[COLUMN3] OTHERWISE[COLUMN1] endif.

1	2	3
ab	ad	ca
	ma
cd
		tb
cb		dk

Thanks

Rojan

[atcodedog05]

Hi @rojan 

Here is how you can do it.

Workflow:

Hope this helps : )

[rojan]

HI,

if COLUMN  1 , 2, 3 data have i i want to pick up from column 2. and in above formula its data capturing column 3

[atcodedog05]

Hi @rojan 

Check this one out.

Workflow:

Hope this helps : )

[atcodedog05]

Hi @rojan 

If this doesnt meet the requirement can you please provide expected output. We can take a look into it.

[apathetichell]

@rojanyour original formula said that if column 2 was blank you wanted column 1 - else you wanted column 3. Column 2 data was not captured in either scenarios. If this isn't the case - what do you want?

[HomesickSurfer · Accepted solution]

Hi @rojan 

This updates [2] in priority of [2],[3],[1] if neither null nor empty.

IF 
!IsEmpty([2]) OR !IsNull([2]) THEN [2] 
ELSEIF 
IsEmpty([2]) OR IsNull([2]) AND IsEmpty([3]) OR IsNull([3]) THEN [1] 
ELSEIF 
IsEmpty([2]) OR IsNull([2]) AND !IsEmpty([3]) OR !IsNull([3]) THEN [3]
ELSE [2] ENDIF

[atcodedog05]

Hi @HomesickSurfer

Just an info share IsEmpty() also covers IsNull() 🙂

Good catch on the priority order 🙂👍

[HomesickSurfer]

Hi @atcodedog05 

Good to know!  Thx.

@rojan , another approach, though ideal and somewhat overkill, is to reorder your fields in 2,3,1 order,  crosstab, then use a summary tool to identify 'first' non-blank value.

[atcodedog05]

Hi @rojan 

Taking reference from @HomesickSurfer approach. You can order if statements in the priority order. You can rearrange as required.

IF !IsEmpty([2]) THEN [2] // First priority
ELSEIF !IsEmpty([1]) THEN [1] // Second priority
ELSEIF !IsEmpty([3]) THEN [3] // Thrid priority
ELSE Null() ENDIF  

Hope this helps : )