Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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