0 Replies Latest reply: Oct 26, 2017 3:36 PM by Richard Judkins RSS

    Combine two fields as one row in pivot table

    Richard Judkins

      Hi all

       

      This is a double barrelled question. And to clarify, this relates to Qlik Sense Server.

       

      I have two fields I have created using CASE statements in the Data load: Partner_Referred and Partner_Onsell

      FYI, the CASE statements are here:

             ,CASE when ACC_SEGMENT not like '%Growing%' then 'Y' else null end as Partner_Onsell

             ,CASE when REF_CLIENT_ID <> '' then 'Y' else null end as Partner_Referred

       

      As far as the pivot tables are concerned, Partner_Referred has an associated Measure - Count(DISTINCT(REF_CLIENT_ID))

      And Partner_Onsell has Count(DISTINCT(CLIENT_ID))

       

      When I have them in two separate pivot tables, it works swimmingly.

       

      However, I need to combine these two fields into one row on a single pivot table.

       

      Something like a MATCH: if(Match(Partner_Referred, Partner_Onsell,'*Y*'),'Partner Referral',null). Except that doesn't work at all.

       

      Plus, while I have a DISTINCT count of REF_CLIENT_ID and CLIENT_ID separately, there are a number of ID's that appear in both results.

      I need to have a DISTINCT count of ID's from both these fields.

      For example, there may be 20 distinct ID's in both fields. 5 ID's however appear in both fields. So the figure that should show in the pivot is 35.

       

      Any ideas please?