Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator II
Creator II

Set Analysis with multiple different conditions

Hi,

I have

Table1

coursecompletion:

Load * Inline [

CouseCompletionID,lender_offer_id, final_test_status, course_completed ,survey_completed

1,1,Passed,1,1

2,2,Failed,0,0

3,3,Passed,1,0

4,4,Passed,1,0

5,5,Passed,1,1  

6,6,Passed,1,1

7,7,Passed,1,0];

Table2

certificateaccesslog:

Load * Inline [

certificateaccesslogId,lender_offer_id,lender_offer_id1,certificate_download_count

1,1,1,0

2,2,2,2

3,1,1,1

4,2,2,3

5,4,4,0];

Table3

useractivitylog:

Load * Inline [

useractivitylogId,lender_offer_id,lender_offer_id2 ,log_type_id

1,2,2,12

2,3,3,23

3,6,6,1

4,5,5,2];

From above tables i need to get count of lender_offer_id (where lender_offer_id has final_test_status = Passed and course_completed = 1)  which is having survey_completed = 0,certificate_download_count=0, and log_type_id  not = 23.

I mean count of  passed and course completed lender_offer_id not involved in any action like survey completion, certificate download and not having log_type_id 23.

In above example i should get count as 2, because lender_offer_id  4 and 7  is not involved in any action. i.e 4 and 7 dont have log_type_id = 23, certificate_download_count =1, survey_completed =1;

Please help on this.

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Got it..

Check below Set Analysis Expression. I think this should work..

//For Count

=Count(DISTINCT

  {<

  lender_offer_id = p({1<survey_completed = {0}>}lender_offer_id)

  *

  e({1<certificate_download_count = {">0"}>}lender_offer_id)

  *

  e({1<log_type_id = {23}>}lender_offer_id)

  *

  e({1<certificate_download_count = {">0"}>}lender_offer_id)

  >}

lender_offer_id)

// and Lender Offer ID

=Concat(

  {<

  lender_offer_id = p({1<survey_completed = {0}>}lender_offer_id)

  *

  e({1<certificate_download_count = {">0"}>}lender_offer_id)

  *

  e({1<log_type_id = {23}>}lender_offer_id)

  *

  e({1<certificate_download_count = {">0"}>}lender_offer_id)

  >}

DISTINCT lender_offer_id, ', ')

View solution in original post

9 Replies
MK_QSL
MVP
MVP

How its 2?

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Supriya,

I don't have enough time to build the solution for you, but I will give you some pointers:

1. Counting key fields is tricky, because you never know what table are you counting from. So, from this perspective, you might have to duplicate your key fields and create unique non-key fields for the purpose of counting.

2. In your set analysis, you will need to combine multiple conditions that might have to be satisfied in multiple tables and possibly different rows of the same table. The best way of doing that is using the functions P() and E(). You can combine several P() functions with different conditions - something like this:

{<Offer_ID=P({<final_test_status={Passed}>} Offer_ID1) * P({<certificate_download_count={">0"}>} Offer_ID2) >}

Something along these lines.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

berryandcherry6
Creator II
Creator II
Author

Hi,

Thanks for reply

because lender_offer_id  4 and 7  is not involved in any action. i.e 4 and 7 have  log_type_id  != 23, certificate_download_count !=1, survey_completed != 1;


Let me know if you need any clarification

berryandcherry6
Creator II
Creator II
Author

Hi troyansky

As you directed,

1. you might have to duplicate your key fields and create unique non-key fields for the purpose of counting.

    I made lender_offer_id  in different Table as Unique keys.

   One more thing i need to Clarify here , when we load tables with same field names(column) tables will get concatenated in Qliksense,       So we get only one fieldname with lender_offer_id  i.e all data from three tables will be present in this. So why we need to make it as          duplicate and Unique keys ?.

2.You can combine several P() functions with different conditions

  I made set analysis as below, with E() to exclude lender_offer_id


count({<lender_offer_id=P({<final_test_status={Passed}>} lender_offer_id) * E({<survey_completed={"0"}>} lender_offer_id)*E({<certificate_download_count={"0"}>} lender_offer_id)*P({<log_type_id-={"23"}>} lender_offer_id) >}lender_offer_id)


This gives me Zero. Which is not correct.


Can you Please explain what is going wrong in my code. It will be very helpful. And Please check attached qvf file

jonathandienst
Partner - Champion III
Partner - Champion III

Unpacking your code:

Count({<

  lender_offer_id =

  P({<final_test_status={Passed}>} lender_offer_id)

  *

  E({<survey_completed = {"0"}>} lender_offer_id)  //

  *

  E({<certificate_download_count = {"0"}>} lender_offer_id)

  *

  P({<log_type_id -= {"23"}>} lender_offer_id)

>} lender_offer_id)

Check what each term of the SA expression returns. I suspect that

  • E({<certificate_download_count = {"0"}>} lender_offer_id) will return 4, but not 7 as certificate_download_count is a missing value for lender_offer_id 7
  • P({<log_type_id -= {"23"}>} lender_offer_id) will not return 4 or 7 as both are missing values for log_type_id.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MK_QSL
MVP
MVP

Got it..

Check below Set Analysis Expression. I think this should work..

//For Count

=Count(DISTINCT

  {<

  lender_offer_id = p({1<survey_completed = {0}>}lender_offer_id)

  *

  e({1<certificate_download_count = {">0"}>}lender_offer_id)

  *

  e({1<log_type_id = {23}>}lender_offer_id)

  *

  e({1<certificate_download_count = {">0"}>}lender_offer_id)

  >}

lender_offer_id)

// and Lender Offer ID

=Concat(

  {<

  lender_offer_id = p({1<survey_completed = {0}>}lender_offer_id)

  *

  e({1<certificate_download_count = {">0"}>}lender_offer_id)

  *

  e({1<log_type_id = {23}>}lender_offer_id)

  *

  e({1<certificate_download_count = {">0"}>}lender_offer_id)

  >}

DISTINCT lender_offer_id, ', ')

berryandcherry6
Creator II
Creator II
Author

Hi,

Yes Jonathan, its returning same lender_id's like you suspected.

Concat({<

lender_offer_id =

  P({<final_test_status={Passed},course_completed={'1'}>} lender_offer_id) *

  E({<survey_completed = {"1"}>} lender_offer_id) *

  E({<certificate_download_count = {">0"}>} lender_offer_id1)

>} lender_offer_id, ', ')   returns 4

Checking on Manish Kacchia's answer.....

berryandcherry6
Creator II
Creator II
Author

Hi,

Thanks for your responses

@Manish Kachhia, small addition to your code, As i want to get counts only who are passed course i need to check

final_test_status={'Passed'},course_completed={'1'}, So

Count(DISTINCT

  {<

  lender_offer_id = p({1<final_test_status={'Passed'},course_completed={'1'},survey_completed = {0}>}lender_offer_id)

  *

  e({1<certificate_download_count = {">0"}>}lender_offer_id)

  *

  e({1<log_type_id = {23}>}lender_offer_id)

  *

  e({1<certificate_download_count = {">0"}>}lender_offer_id)

  >}

lender_offer_id)


@jontydkpi , your way of explaining how to debug, helped me to know how expression was working and how it is. Thanks.


@Oleg Troyansky

Thanks for your idea/pointers for mentioning how to tackle my problem. It was a great idea and help

You were pointing to take key values duplicate and unique. I didnt get point 1 of him.


I request  to answer my query when you find free time, even if delayed no problem. As i am fresher, i need to understand what community members tell us and their point of view to solve problem.


Thanks,

Supriya



Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

What I meant is that counting key fields is ambiguous - for example, when you count CustomerID, do you want all Customers from the Customers table, or all Customers that have Orders in the Orders Table? Or all Customers that have overdue invoices in the Overdue table? In order to avoid ambiguity, you can load the same key fields with a different name and count those new unique fields instead:

Customers:

LOAD

   CustomerID,

   CustomerID as CustomerMasterCounter

.....

Orders:

LOAD

   CustomerID,

   CustomerID as CustomerOrderCounter

...

cheers,

Oleg Troyansky

Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense