How to Do Reduction on 2 Different Fields

trstevens — Sat, 16 Nov 2024 21:39:21 GMT

Hello,

I have been browsing the website and I have not been able to understand how to do two different reductions for different fields.

I have 2 different datasets that must have reductions on their respective fields.

I have tried to Alias the second reduction. The Alias brings back no errors but I don't get the specified security that I need.

Here is my Section Access:

SECTION ACCESS;
SELECT
CASE WHEN Active = 0 THEN 'dummy' ELSE Access END AS [ACCESS],
CASE WHEN Active = 0 THEN 'dummy' ELSE UserId END as [USERID],
REDUCTION,
REDUCTION AS REDUCTION1
FROM BrokerFlow_NEW..QlikAccess;
SECTION APPLICATION;



I Pull one of my reductions like so: 

[AGENTBROKER]:
LOAD Autonumber(Agent &'_' & AGENTID) as %keyAgentID, Autonumber(Agent &'_' & AGENTID &'_' & Broker & '_' & Coverage) as %keyAgentBrokerCoverageID, *;
SELECT 
B.Agent, 
B.AGENTID, 
B.Broker, 
B.Broker as REDUCTION,
B.Coverage, 
B.SubmitDate, 
Case 
WHEN B.SubmitDate > DATEADD(D, -180, GETDATE()) THEN 0
WHEN B.SubmitDate BETWEEN DATEADD(D, -365, GETDATE()) AND DATEADD(D, -181, GETDATE()) THEN 1
WHEN B.SubmitDate BETWEEN DATEADD(D, -730, GETDATE()) AND DATEADD(D, -366, GETDATE()) then 2
else 3
end as SubmitBucket
FROM (
SELECT A.Agent, A.AGENTID, A.Broker, A.Coverage, A.SubmitDate, ROW_NUMBER() Over (Partition By A.AGENTID, A.Coverage ORDER BY A.SubmitDate DESC ) as Row
FROM (
SELECT 
P.Name as Agent,
P.ProducerID as AGENTID,
U.Name as Broker,
C.Description as Coverage,
MAX(CONVERT(DATE,Q.Received)) AS SubmitDate
FROM
CIS..Quote Q 
LEFT JOIN CIS..UserID U ON Q.AcctExec = U.UserID
LEFT JOIN CIS..Producer P ON Q.ProducerID = P.ProducerID
LEFT JOIN CIS..Coverage C ON Q.CoverageID = C.CoverageID
WHERE Q.DivisionID IN ('1','2','6','3')
GROUP BY P.Name,P.ProducerID, U.Name, C.Description
HAVING P.Name IS NOT NULL AND U.Name IS NOT NULL AND P.ProducerID <> 'AGT002') A ) B WHERE B.Row = 1;

Here is the second reduction:

LEFT JOIN 
(
SELECT 
A.Id as StrataAccount_ID,
A.name as AccountName,
A.AccountCode,
A.AIM_ID as AccountAimID,
ISNULL(SB.Name, 'No Broker Assigned') as StrataBroker,
ISNULL(SB.Name, 'No Broker Assigned') as REDUCTION1
FROM tbl_Accounts A
LEFT JOIN tbl_Inter_Broker_Account IB ON A.Id = IB.Account_ID AND IB.Active = 1
LEFT JOIN tbl_Broker SB ON IB.Broker_ID = SB.BrokerID
WHERE EXISTS (SELECT 1 FROM tbl_Inter_Account_Program_Locations I WHERE I.Account_ID = A.Id AND I.Policy_ID > 20)
) AS StrataAccount ON StrataLocation.LocationAccountID = StrataAccount_ID

Both B.Broker and SB.Name are virtually the same field but from different tables.

How do I go about this so I can have security where whatever USERID is logging into QlikSense they can only see the information associated with their User ID?

The Security is not working .

Many thanks if you can help me with this

topic How to Do Reduction on 2 Different Fields in App Development

How to Do Reduction on 2 Different Fields