AnsweredAssumed Answered

What have I overlooked in my "neither-nor" Case-Function?

Question asked by jobemay on Feb 10, 2015
Latest reply on Feb 11, 2015 by jobemay

What have I overlooked in my „neither-nor“ calculation?

 

I have 4 fields in my dictionary:  FIELD1FIELD2, FIELD3, FIELD4.

Some records have values for all 4 fields, some only for some fields.

The empty fields display a symbol denoting „empty“: „ø“

 

Example:

 

Record1: FIELD1: abc,   FIELD2: def,   FIELD3: ghi,   FIELD4: jkl

 

Record2: FIELD1: aaa,   FIELD2: bbb,   FIELD3: ccc,   FIELD4: ddd

 

Record3: FIELD1: ø,     FIELD2: fff,   FIELD3: ggg,   FIELD4: hhh

 

Record4: FIELD1: mma,   FIELD2: ø,     FIELD3: mmc,   FIELD4: mmd

 

Record5: FIELD1: ø,     FIELD2: ø,     FIELD3: xzz,   FIELD4: zzz

 

Now I want to sort the records alphabetically using a sort field SORTFIELD.

a) If FIELD1 contains a value it should be used in the SORTFIELD.

b) If it does not then FIELD2 should be used  in the SORTFIELD.

c) If neither FIELD1 nor FIELD2 contain a value then FIELD3 should be used in the SORTFIELD.

 

 

 

So the result should be:

 

 

Record1: FIELD1: abc,   FIELD2: def,   FIELD3: ghi,   FIELD4: jkl   SORTFIELD: abc

 

Record2: FIELD1: aaa,   FIELD2: bbb,   FIELD3: ccc,   FIELD4: ddd   SORTFIELD: aaa

 

Record3: FIELD1: ø,     FIELD2: fff,   FIELD3: ggg,   FIELD4: hhh   SORTFIELD: fff

 

Record4: FIELD1: mma,   FIELD2: ø,     FIELD3: mmc,   FIELD4: mmd   SORTFIELD: mma

 

Record5: FIELD1: ø,     FIELD2: ø,     FIELD3: cba,   FIELD4: cbb   SORTFIELD: cba

 

(And of course the records sorted should be:

 

Record2: FIELD1: aaa,   FIELD2: bbb,   FIELD3: ccc,   FIELD4: ddd   SORTFIELD: aaa

Record1: FIELD1: abc,   FIELD2: def,   FIELD3: ghi,   FIELD4: jkl   SORTFIELD: abc

Record5: FIELD1: ø,     FIELD2: ø,     FIELD3: cba,   FIELD4: cbb   SORTFIELD: cba

Record3: FIELD1: ø,     FIELD2: fff,   FIELD3: ggg,   FIELD4: hhh   SORTFIELD: fff

Record4: FIELD1: mma,   FIELD2: ø,     FIELD3: mmc,   FIELD4: mmd   SORTFIELD: mma)

 

It was easy to obtain a) and b) with the calculation: Case ( FIELD1 = "ø"; FIELD2 ; FIELD1),

but whatever I tried with „and“ or „&“ did not work to get c). I suppose I have overlooked something very basic, but what?

j

Outcomes