I have following script.
I am using NTNAME for access.
Step1:
In ConfigUsers table I am populating the Country which is assigned to the user.
ConfigUsers table data
NTNAME,Country
User1,US
User2,UK
User1,Spain
User3,France
Step2 : I am loading Orders table in Orders.
Step3: Creating Mapping Load Table from existing ConfigUsers. Here data is correctly populated for e.g. for User1 2 rows US and Spain are populated in Config2 table. I have checked this by creating tablebox for Config2 table.
Step4: Creating Sale table. Here I am trying to display all records from Orders table(created in Step2). But if ShipCountry is found in Config2 table(created in Step3) then display Country else display '****'.
Similarly if ShipCountry is found in Config2 table(created in Step3) then display CustomerId and so on
Question:
When User1 access, there are two rows (US and Spain) in Config2 table (I have checked this by creating tablebox for Config2 table), those Shipcountry value is displayed correctly.
But those ShipCountry which is not assigned to User1, I want to display '****', but it shows respective Country values instead of display '****'.
Similarly I want to display CustomerID, EmployeeID value only if the ShipContry is found in Config2 table else display '****'..
Similar thing should happen if User2,User3 access the .qvw file.
I have also tried VLOOKUP, but it also doesn't work.
Please Suggest.
OLEDB CONNECT TO ConnectionString
//Step1
ConfigUsers:
SQL SELECT NTNAME,[Country]
FROM tbl_Entitlement;
//Step2
Orders:
SQL SELECT OID,[OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],[ShipVia],[Freight],[ShipName],
[ShipAddress],[ShipCity],[ShipRegion],[ShipPostalCode],[ShipCountry]
FROM Orders;
//Step3
Config2:
mapping load Country AS ShipCountry,Country Resident ConfigUsers;
//Step4
Sale:
Load
ApplyMap('Config2', ShipCountry,'***') as ShipCountry,
if(IsNull( ApplyMap('Config2', ShipCountry,null())) ,'****',[CustomerID]) as CustomerID,
if(IsNull( ApplyMap('Config2', ShipCountry,null())) ,'****',[EmployeeID]) as EmployeeID,
,[RequiredDate],[ShippedDate]
Resident Orders;
Drop Table Orders;
