Qliksense中的SQL逻辑

Question

我想将客户端IP表和PEP表合并成两个字段。

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

在上面的qliksense表中，每当客户机ID重复时，我应该能够选择'Yes‘值，并放弃'No’

在逻辑之后，我的答案应该是1是2否

这有可能实现吗？

Answer 1

如果您只想在所有值都为No和Yes时显示no，否则在SQL中，我将这样做

 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

Answer 2

不确定您是否在查看只使用Qlik的解决方案，但下面的脚本是如何在Qlik脚本中实现该解决方案的示例。

作为这些脚本的结果，将有2个表(由Client ID字段链接)：

• RawData -每个客户端
• MaxData多行，每个客户端有一行，多一个字段指示客户端是否重复

选项1:计数客户

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]
;

选项2:使用 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;

Answer 3

希望这对你有用：

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