topic Complex If-Then statement duplicates lines in QlikView

Complex If-Then statement duplicates lines

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Complex If-Then statement duplicates lines

adamdavi3s — Tue, 10 Jan 2017 12:41:27 GMT

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)

Re: Complex If-Then statement duplicates lines

trdandamudi — Tue, 10 Jan 2017 12:42:43 GMT

May be as below:

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

if (PERSONNEL_CATEGORY = 'Assistant', PERSONNEL_NAME))

Re: Complex If-Then statement duplicates lines

marcus_sommer — Tue, 10 Jan 2017 12:45:10 GMT

Try it with:

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

- Marcus

Re: Complex If-Then statement duplicates lines

MindaugasBacius — Tue, 10 Jan 2017 14:15:29 GMT

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

Re: Complex If-Then statement duplicates lines

Anonymous — Tue, 10 Jan 2017 20:02:41 GMT

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?

Re: Complex If-Then statement duplicates lines

Anonymous — Tue, 10 Jan 2017 20:03:05 GMT

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

Re: Complex If-Then statement duplicates lines

marcus_sommer — Wed, 11 Jan 2017 08:24:30 GMT

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