topic Re: How to Exclude Records in QlikView

How to Exclude Records

Tue, 18 Mar 2014 17:21:50 GMT

Hi,

I am using an IF function to identify a list of customers in the Master Table who satisfy certain criteria. I want use the output of this IF function, which contains list of customers, as input to another function to Exclude these list of customers from the Master Table. Can someone please suggest how to achieve this in QlikView. I have fair programming knowledge, but I am not familiar with QlikView syntax. I appreciate any help.

I have inluded a test data below. It lists 5 customers. I want to count only those customers who do not have license L9. I used the If (License = 'L9', (count(distinct [Customer Lits]))) comand to get the list of customers who Have L9 licenses. I now struggling to exclude this customers from the main list.

Unique Customer ID Customer List License

1 CustA L1

1 CustA L2

1 CustA L3

1 CustA L9

2 CustB L1

3 CustC L2

3 CustC L3

3 CustC L9

4 CustD L3

4 CustD L9

5 CustE L3

5 CustE L5

The Final Answer must list two customers - Cust B and Cust E, because they do not have license L9. The expression I wrote exclude "records" for Cust A with L9, but counts Cust A with L1, L2, and L3 as seperate records.

I expected to see following the bar chart. Bar Chart will have

Licenses -> Dimensions

Customer List -> Expressions.

X-Axis -> Licenses

Y-Axis -> Count(distinct[Customer List])

Expected result:

L1 -> 1

L3 -> 1

L5 -> 1

where Cust B has L1 and CustE has L3 and L5 licenses

Additional Challenge:

If L1 is the oldest license and L9 latest licence, how to display the latest license after you apply the above filter?

If you apply the logic on the output on the above result, the Expected Result in bar chart is:

L1 - 1

L5 - 1

Cust B has only one license L1

Cust E has L5 as the latest license

Thanks,

Re: How to Exclude Records

Gysbert_Wassenaar — Tue, 18 Mar 2014 17:26:34 GMT

Add the field Customer List to a straight table as dimension. Next add one expression : sum({<License-={L9}>}1). Finally hide the expression on the Presentation tab.

Re: How to Exclude Records

Tue, 18 Mar 2014 19:41:03 GMT

Hi Gysbert,

Thanks for your prompt response. After applying your logic I cannot see the unique Customer in the bar Chart. I expected to see following the bar chart. Bar Chart will have Licenses as Dimensions, and Customer List as Expressions.

X-Axis -> Licenses

Y-Axis -> Count(distinct[Customer List])

Expected result:

L1 -> 1

L3 -> 1

L5 -> 1

where Cust B has L1 and CustE has L3 and L5 licenses

PS: I have added one more requirement to my originla question. Display latest licenses

Re: Re: How to Exclude Records

Gysbert_Wassenaar — Tue, 18 Mar 2014 20:05:35 GMT

See attached qvw.

Re: How to Exclude Records

maxgro — Tue, 18 Mar 2014 20:11:44 GMT

dimension License

expression count(distinct {$<CustomerList = E({1<License={L9}>} CustomerList)>} CustomerList )

Re: How to Exclude Records

Tue, 18 Mar 2014 22:19:10 GMT

Hi Massimo,

Many thanks for your response. The logic worked. I appreciate your quick response.

Thanks,

Raghu

Re: How to Exclude Records

maxgro — Wed, 19 Mar 2014 07:08:26 GMT

Please close this discussion by giving Correct and/or Helpful answers to the posts which are useful for you. It helps others in finding answers for similar scenarios. Thanks.

Re: How to Exclude Records

Wed, 19 Mar 2014 13:23:37 GMT

Hi Massimo,

I marked your response with correct answer.

Thanks,

Raghu