Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

biggusdoggus
Contributor

Combine two fields as one row in pivot table

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?