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
Solved! Go to Solution.
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
Hi @rojan
If this doesnt meet the requirement can you please provide expected output. We can take a look into it.
@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?
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
Just an info share IsEmpty() also covers IsNull() 🙂
Good catch on the priority order 🙂👍
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.
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 : )