topic Re: CASE Statement Question in App Development

CASE Statement Question

alexis — Wed, 28 Sep 2022 13:22:26 GMT

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

Re: CASE Statement Question

Or — Wed, 28 Sep 2022 13:53:34 GMT

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

Re: CASE Statement Question

alexis — Wed, 19 Oct 2022 09:16:32 GMT

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

Re: CASE Statement Question

Or — Wed, 19 Oct 2022 10:19:39 GMT

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).

Re: CASE Statement Question

alexis — Tue, 25 Oct 2022 15:00:46 GMT

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