CASE Statement Question

[alexis]

I am reading a field (Let's call it "ReasonCode") from a table that contains a range of values like "A1", "A2","A3", "A4", "A5", "B1", "B2", "B3", "B4", "B5", "C1", "C2", "C3", "C4", "C5", "D1" as well as blank or Null().,

The brief is to create a new field (or just replace ReasonCode) that contains values that meet the following rules:

• If  "A1", "A2", "A3", "B1", "B2" or "B3" then carry these values to the new field
• If Null() or blank then the new field contains blank or Null()
• If any of the other values (but not blank or null) then the new field to contain "OTHER"

Any idea what the most efficient way is to do this in Qlik?

Regards and thanks in advance

Alexis

[Or · Accepted solution]

If(Len(Field)=0 OR Match(Field,'A1','A2', 'A3','B1','B2','B3'),Field,'OTHER') should work here.

[alexis]

Thank you Or for your suggestion which worked absolutely fine!

If, instead of hard-coding all the codes that we are testing against (A1, A2 etc in the example), do you have any suggestions on how this can be modified to read the list from a table (say an Excel spreadsheet or a Mapping LOAD table for example) as the list of codes is likely to change from time to time?

Thanks again

Alexis

[Or · Accepted solution]

Sounds like mapping load would be an option, something along the lines of:

If(Len(Field)=0 OR NOT ISNULL( ApplyMap('MappingLoadTable',Field,null())),Field,'Other')

Though I imagine there's any number of other ways to achieve this (such as two separate loads with Exists/Not Exists, or a second pass load after the values have been joined/mapped in, and probably other ways as well).

[alexis]

Thanks for the suggestion - although it didn't quite work, it gave me the idea to construct my own version which is a simplified version of yours (I removed some application-specific variations for inclusion here):

if (Len(Field)>0,
    /// Some if conditions here that are special cases of 'Field'
    If(Field = 'Some Special Value, 'Special',
        ApplyMap('MappingLoadTable', Field, 'Unknown'))) As Field_Text

Thanks again
Alexis