Reputation: 101
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
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):
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
Reputation: 1059
Hope this works for you:
select clientId, max(PEP)
from ip
group by clientId
Upvotes: -1
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