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?
