2 Replies Latest reply: Aug 24, 2017 6:58 PM by John Henry RSS

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

        • Re: How to create a grouped Dimension using data load editor?
          Jaime Aguilar García

          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