Search code examples
sqlqliksense

SQL logic in Qliksense

I want to consolidate the client IP table and the PEP table into just two fields.

|=========================|
|  Client ID |    PEP     |
|=========================|
|      1     |    Yes     |
|      1     |     No     |
|      2     |    Yes     |
|            |            |
==========================

In qliksense SQL table above, whenever the client ID repeats, I should be able to select the 'Yes' values and discard the 'No'

After the logic, my answer should only be 1 Yes 2 No

Is this possible to implement?

Solution

  • If what you want is to show No only if all values are no and Yes otherwise in SQL I would do it like this

     SELECT ID
   CASE WHEN SUM(CASE WHEN PEP = 'Yes' THEN 1 ELSE 0 END) > 0 THEN 'Yes'
        ELSE 'No END AS PEP
 FROM Some_table_you_did_not_name
 GROUP BY ID