Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am new to Qlik Sense and want to create a new field which displays values based on the different conditions in two existing fields [Status] & [State].
Field 1: [Status] | Field 2: [State] | New field: [Display] |
Draft | Not Executed | Draft |
Ready for Approval | Not Executed | Draft |
Approved | Not Executed | Draft |
Approved | Executed | Approved |
Varied | Executed | Varied |
Ready for Approval | Executed | Varied |
Inactive | Executed | Inactive |
Inactive | Not Executed | Inactive |
Varied | Not Executed | Draft |
I have been trying to build this with If statements but have not been successful - appreciate some assistance.
Why don't you use a mapping table to do this
MappingTable:
Mapping
LOAD Status&State,
Display
FROM ....;
FactTable:
LOAD ....,
ApplyMap('MappingTable', Status&State, 'N/A') as NewField,
....
FROM ....
Hi Sunny,
As mentioned, I am new to Qlik and my access only allows me to add fields and create expressions to new sheets (cannot upload data).
I created the following Mapping Table but get an 'Error in expression':
Mapping Table:
mapping LOAD * inline [
Status, State
'Draft', 'Not Executed'
'Ready for Approval', 'Not Executed'
'Approved', 'Not Executed'
'Approved', 'Executed'
'Varied', 'Not Executed'
'Varied', 'Executed'
'Ready for Approval', 'Executed'
'Inactive', 'Executed'
'Inactive', 'Not Executed'
],
Display:
LOAD*,
ApplyMap (‘Draft’, ‘Not Executed’) as ' Draft'
ApplyMap (‘Ready for Approval’, ‘Not Executed’) as ' Draft'
ApplyMap (‘Approved’, ‘Not Executed’) as ' Draft'
ApplyMap (‘Approved’, ‘Executed’) as ' Approved'
ApplyMap (‘Varied’, ‘Executed’) as 'Varied'
ApplyMap (‘Ready for Approval’, ‘Executed’) as ' Varied'
ApplyMap (‘Inactive’, ‘Executed’) as 'Inactive'
ApplyMap (‘Inactive’, ‘Not Executed’) as 'Inactive'
ApplyMap (‘Varied’, ‘Not Executed’) as 'Draft'