Complex If-Then statement duplicates lines

[Anonymous]

Hi!

I'm trying to show information for two personnel categories in a single column -- basically, if the data exists, show it.  There are no circumstances in which both categories would exist simultaneously.

I can write a simple If statement that works.  The problem is figuring out how to combine them as a pair of IF statements related via an OR.  If I write one for each, then my data lines duplicate.  That doesn't help me for many reasons.

Each of these work individually:

  =if (PERSONNEL_CATEGORY = 'Assistant Surgeon', PERSONNEL_NAME)

  =if (PERSONNEL_CATEGORY = 'Assistant', PERSONNEL_NAME)

I tried

  =if  (PERSONNEL_CATEGORY = 'Assistant Surgeon', PERSONNEL_NAME) or if (PERSONNEL_CATEGORY = 'Assistant', PERSONNEL_NAME)

-- I get blanks in all data sets (but no duplication)

I tried

  =if ( (PERSONNEL_CATEGORY = 'Assistant Surgeon', PERSONNEL_NAME) OR (PERSONNEL_CATEGORY = 'Assistant', PERSONNEL_NAME) )

-- and then get an error in calculation. 

I tried

  =if (PERSONNEL_CATEGORY = 'Assistant Surgeon' or 'Assistant', PERSONNEL_NAME)

-- and it just shows me whichever category I put first.  If I put the criteria within parentheses, I get blanks.

Should I pick a different function?  (one of the Get functions?)  Or am I just messing up my equation?  I've looked at this so many times that it's probably staring me in the face...

Thanks!

Chris

[adamdavi3s]

What is wrong with this outcome? sorry hard to picture exactly what you are after

Sorry just read the 'all data sets' being blank bit

  =if  (PERSONNEL_CATEGORY = 'Assistant Surgeon', PERSONNEL_NAME) or if (PERSONNEL_CATEGORY = 'Assistant', PERSONNEL_NAME)

-- I get blanks in all data sets (but no duplication)

What about

if (PERSONNEL_CATEGORY = 'Assistant Surgeon' or PERSONNEL_CATEGORY ='Assistant', PERSONNEL_NAME)

[trdandamudi]

May be as below:

  =if  (PERSONNEL_CATEGORY = 'Assistant Surgeon', PERSONNEL_NAME,

        if (PERSONNEL_CATEGORY = 'Assistant', PERSONNEL_NAME))

[marcus_sommer · Accepted solution]

Try it with:

if(match(PERSONNEL_CATEGORY, 'Assistant Surgeon', 'Assistant'), PERSONNEL_NAME, '#NV')

- Marcus

[MindaugasBacius]

All the above answers seem to work.

I strongly recommend you reading about Nested If Statement and Logical operators.

Few links as not to mention other that you could google:

Logical operators ‒ QlikView

Nested IF Statements

[Anonymous]

Thanks to all who responded -- the specified syntax worked in terms of combining the data into a single column.  But it still is duplicating data on the table -- It will show the line for the case without an assistant and then will show the exact same line including the assistant's name.  It only does this for cases with an assistant

Thoughts?

[Anonymous]

And a big thank you for sending me to educational locations!

[marcus_sommer]

The duplicating data issue isn't quite clear. Please provide for it a screenshot of the used chart and from the tableviewer and of course how the final expression(s) look like.

- Marcus