9 Replies Latest reply: Apr 18, 2017 10:33 PM by Oleg Troyansky RSS

    Set Analysis with multiple different conditions

    Supriya R

      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.

          • Re: Set Analysis with multiple different conditions
            Supriya R

            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

              • Re: Set Analysis with multiple different conditions
                Manish Kachhia

                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, ', ')

                  • Re: Set Analysis with multiple different conditions
                    Supriya R

                    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)


                    @Jonathan Dienst , 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



              • Re: Set Analysis with multiple different conditions
                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

                  • Re: Set Analysis with multiple different conditions
                    Supriya R

                    Hi Oleg 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

                      • Re: Set Analysis with multiple different conditions
                        Jonathan Dienst

                        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.
                          • Re: Set Analysis with multiple different conditions
                            Supriya R

                            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.....