Set Analysis with multiple different conditions

[berryandcherry6]

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.

[MK_QSL]

How its 2?

[Oleg_Troyansky]

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]

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]

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]

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.

[MK_QSL · Accepted solution]

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]

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]

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]

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