How to create a grouped Dimension using data load editor?

[john_henry]

I have an expression I created using Master Items in Qlik Sense (see syntax below), however, the colors of the graph apparently are not consistent on a calculated dimension.  So I wanted to know how to create this new field using the data load editor so that that data table actually has a new column called "Renewal Status" with the grouped values as shown below.  What should the syntax in the data load editor be?

=If(wildmatch(Renewal_Status_Of_Contract, '02*'),'Converted/Renewed',

if(wildmatch(Renewal_Status_Of_Contract, '05*'), 'Converted/Renewed',

if(wildmatch(Renewal_Status_Of_Contract, '03*'), 'Pending',

if(wildmatch(Renewal_Status_Of_Contract, '04*'), 'Pending',

if(wildmatch(Renewal_Status_Of_Contract, '07*'), 'Pending',

if(wildmatch(Renewal_Status_Of_Contract, '08*'), 'Pending',

if(wildmatch(Renewal_Status_Of_Contract, '06*'), 'Lost',

if(wildmatch(Renewal_Status_Of_Contract, '09*'), 'Lost',

'Other'))))))))

[jaimeaguilar · Accepted solution]

Hi,

you can use almost the same formula in script but you have to assign a name to your field, like this:

Table:

Load Field1,

         Field2,

         if(wildmatch(Renewal_Status_Of_Contract, '02*'),'Converted/Renewed',

if(wildmatch(Renewal_Status_Of_Contract, '05*'), 'Converted/Renewed',

if(wildmatch(Renewal_Status_Of_Contract, '03*'), 'Pending',

if(wildmatch(Renewal_Status_Of_Contract, '04*'), 'Pending',

if(wildmatch(Renewal_Status_Of_Contract, '07*'), 'Pending',

if(wildmatch(Renewal_Status_Of_Contract, '08*'), 'Pending',

if(wildmatch(Renewal_Status_Of_Contract, '06*'), 'Lost',

if(wildmatch(Renewal_Status_Of_Contract, '09*'), 'Lost',

'Other'))))))))  AS STATUS //HERE YOU CAN PUT THE NAME YOU WISH

FROM TABLE;

you just have to find in your load editor the part where "Renewal_Staus_Of_Contract" is loaded,

hope this helps,

best regards

[john_henry]

thanks...works great!