Moh
Moh

Reputation: 101

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?

Upvotes: 0

Views: 194

Answers (3)

Stefan Stoychev
Stefan Stoychev

Reputation: 5012

Not sure if you are looking at Qlik-only solution but the scripts below are an example how it can be achieved in Qlik script

As a result of these scripts there will be 2 tables (linked by Client ID field):

  • RawData - multiple rows per Client
  • MaxData - one row per Client with one more field indicating if the client is repeated or not

Option 1: Count the customers

RawData:
Load * Inline [
 Client ID, PEP
 1        , Yes
 2        , Yes
 1        , No 
];

MaxData:
Load
  [Client ID],
  if(ClientCount > 1, 'Yes', 'No') as Repeat
;
Load
  [Client ID],
  count([Client ID]) as ClientCount
Resident 
  RawData
Group By
  [Client ID]
;

Option 2: use peek function

RawData:
Load * Inline [
 Client ID, PEP
 1        , Yes
 2        , Yes
 1        , No 
];

// Load the same data but order it by the Client ID column
// create new field (RepeatsTemp) which compares the current Client ID value 
// with the previous one. If they are equal assign 1 else 0
TempData:
Load
  [Client ID],
  PEP,
  if([Client ID] = Peek([Client ID]), 1, 0) as RepeatsTemp
Resident
  RawData
Order By
  [Client ID]
;

// Load the TempData table and get the max of RepeatsTemp for 
// each Client ID (group by by Client ID)
// in the preceeding load "replace" 1 and 0 from the repeat field
// with 'yes' and 'no'
MaxData:
Load
  [Client ID],
  if(MaxRepeats = 1, 'Yes', 'No') as Repeats
;
Load
  [Client ID],
  max(RepeatsTemp) as MaxRepeats
Resident
  TempData
Group by
  [Client ID]
;

// we dont need this table anymore
Drop Table TempData;

Upvotes: 1

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

Hope this works for you:

select clientId, max(PEP)
from ip
group by clientId

Upvotes: -1

Hogan
Hogan

Reputation: 70523

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

Upvotes: 1

Related Questions