Field with the Opposite of a Value

[#1]

Hello!

I have a table with the following data:

ACCOUNT_ID	STATUS
001	PLANNED
002	INSPECTED
003	SUSPENDED
004	SELECTED
005	PLANNED
006	SELECTED
007	PLANNED
008	SUSPENDED
009	SELECTED

What I want is to create a new field, OPPOSITE_STATUS with the following values:

• NOT PLANNED: I see those ACCOUNT_IDs with another STATUS than PLANNED
• NOT INSPECTED: I see those ACCOUNT_IDs with another STATUS than INSPECTED
• NOT SUSPENDED: I see those ACCOUNT_IDs with another STATUS than SUSPENDED
• NOT SELECTED: I see those ACCOUNT_IDs with another STATUS than SELECTED

For example:

If I have a listbox with: NOT PLANNED / NOT INSPECTED / NOT SUSPENDED / NOT SELECTED

And I selected NOT PLANNED, I should see the following ACCOUNT_IDs:

ACCOUNT_ID	STATUS
002	INSPECTED
003	SUSPENDED
004	SELECTED
006	SELECTED
008	SUSPENDED
009	SELECTED

(001, 005 & 007 have the STATUS = PLANNED)

So I thought in creating a field OPPOSITE_STATUS like this (I'd need it in Script):

TABLE:

LOAD

ACCOUNT_ID,

STATUS,

IF(STATUS<>'PLANNED','NOT PLANNED',
     IF(STATUS<>'SELECTED','NOT SELECTED',
          IF(STATUS<>'INSPECTED','NOT INSPECTED',
               IF(STATUS<>'SUSPENDED','NOT SUSPENDED'))))     AS OPPOSITE_STATUS

FROM $(vDATA)PLANIFICATION.qvd (qvd)

Do you think it should work?

Thank you!!!

-----

Oh, I have just tried this but it doesn't work, because I get the value that satisfies the first valid conditional, but only the first one...

I leave the QVW attached.

Thanks.

[SergeyMak]

I think, in this case it's better to use pick and match functions

[SergeyMak]

or just create field  with 'not' like

'NOT ' & STATUS AS OPPOSITE_STATUS

[#4]

But if I do that I get:

ACCOUNT_ID	STATUS	OPPOSITE_STATUS
001	PLANNED	NOT PLANNED
002	INSPECTED	NOT INSPECTED
003	SUSPENDED	NOT SUSPENDED
004	SELECTED	NOT SELECTED
005	PLANNED	NOT PLANNED
006	SELECTED	NOT SELECTED
007	PLANNED	NOT PLANNED
008	SUSPENDED	NOT SUSPENDED
009	SELECTED	NOT SELECTED

And for example, for the STATUS PLANNED I should have the OPPOSITE_STATUS NOT_INSPECTED, NOT_SUSPENDED & NOT SELECTED

[SergeyMak]

And for example, for the STATUS PLANNED I should have the OPPOSITE_STATUS NOT_INSPECTED, NOT_SUSPENDED & NOT SELECTED

but do you understand that you will have three lines in this case? like

ACCOUNT_ID	STATUS	OPPOSITE_STATUS
001	PLANNED	NOT_INSPECTED
001	PLANNED	NOT_SUSPENDED
001	PLANNED	NOT SELECTED

it's probably better to do in a separate table

[#6]

Yes, I know I'd have a table with the different OPPOSITE_STATUS for every ACCOUNT_ID.

But how could I build that table?

Thanks.

[SergeyMak · Accepted solution]

[data]:

LOAD ACCOUNT_ID,

    STATUS

FROM

[http://community.qlik.com/message/547927]

(html, codepage is 1252, embedded labels, table is @1);

NoConcatenate

[statuses]:

LOAD *

Resident data;

LEFT JOIN (statuses)

LOAD * INLINE

[STATUS, Opposit

INSPECTED, NOT PLANNED

INSPECTED, NOT SELECTED

INSPECTED, NOT SUSPENDED

SELECTED, NOT PLANNED

SELECTED, NOT SUSPENDED

SELECTED, NOT INSPECTED

PLANNED, NOT SUSPENDED

PLANNED, NOT INSPECTED

PLANNED, NOT SELECTED

SUSPENDED, NOT SELECTED

SUSPENDED, NOT INSPECTED

SUSPENDED, NOT PLANNED

];

DROP Field STATUS From statuses;

PFA

[sujeetsingh]

I think what you want to implement can be done in many ways.

A lot are suggested by these people and i think your nested if should also work.

[#9]

Thanks for your solution! It works

Although, I'm going to see if I get another solution maybe more performant because there are houndreds of accounts and maybe the application could get some slow.

[#10]

Could you recommend me something performant to use in this case when having many registries? Thanks.