Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Any idea what the most efficient way is to do this in Qlik?
Regards and thanks in advance
Alexis
If(Len(Field)=0 OR Match(Field,'A1','A2', 'A3','B1','B2','B3'),Field,'OTHER') should work here.
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).
If(Len(Field)=0 OR Match(Field,'A1','A2', 'A3','B1','B2','B3'),Field,'OTHER') should work here.
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
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).
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