Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to merge an internal section access table with admin accounts.
The issue is however that even though everything seems correct, it only seems to load the admin account for me (The person performing the reload)
When I comment out the section access, and place the fields in a straight table, everything from my account is identical of the other admin accounts (ACCESS=ADMIN,POSITIONID=*)
However every time the other admin person tries to login in they get the message: 'Access Denied'
What am I doing wrong?
My script goes as follows:
Main Script:
Concat: LOAD "Employee ID" as "PERSONALID", 'EEINTERBREW\'&"Username (pre 2000)" as USERID FROM * (txt, codepage is 28591, embedded labels, delimiter is '\t', msq) Where len("Employee ID">0); Outer Join Concat: AD_WE: LOAD "Employee ID" AS "PERSONALID", 'WEINTERBREW\'&"Username (pre 2000)" AS "USERID" FROM * (txt, utf8, embedded labels, delimiter is ',', msq) Where len("Employee ID">0); Outer Join Concat: AD_CO: LOAD "Employee ID" AS "PERSONALID", 'COINTERBREW\'&"Username (pre 2000)" AS "USERID" FROM * (txt, utf8, embedded labels, delimiter is ',', msq) Where len("Employee ID">0); left Join Concat: EmployeeReport: LOAD Pers.No. as "PERSONALID", Position as HRPOSITIONID FROM * (ooxml, embedded labels, table is Sheet1); outer join Concat: Accessor: LOAD Pers.No. as "PERSONALID", Capitalize("First name"&' '& "Last name") as "Name (Accessor)" FROM * (ooxml, embedded labels, table is Sheet1); Outer Join Concat: Z_RELATION: LOAD RO, // "ID of related object" as ObjectIDZrel, "ID of related object" as "ACCES(TO)", If(RO='S',"ID of related object") as "Pos.IDZ-Rel", ObjectID as "HRPOSITIONID", ObjectID as "Position ID (Accessor)" FROM * (txt, codepage is 28591, embedded labels, delimiter is ',', msq); Outer Join Concat: Normal_Relation1: LOAD ObjectID as "ACCES(TO)", RO as RO1, IF(RO='O',"ID of related object") as Org.ID1, IF(RO='S',"ID of related object") as Pos.ID1 FROM * (txt, codepage is 28591, embedded labels, delimiter is ',', msq); Outer Join Concat: Normal_Relation2: LOAD ObjectID as Org.ID1, "RO" as "RO2", IF("RO"='O',"ID of related object") as "Org.ID2", IF("RO"='S',"ID of related object") as "Pos.ID2" FROM * (txt, codepage is 28591, embedded labels, delimiter is ',', msq); Outer Join Concat: Normal_Relation3: LOAD ObjectID as Org.ID2, "RO" as "RO3", IF("RO"='O',"ID of related object") as "Org.ID3", IF("RO"='S',"ID of related object") as "Pos.ID3" FROM * (txt, codepage is 28591, embedded labels, delimiter is ',', msq); Outer Join Concat: Normal_Relation4: LOAD ObjectID as Org.ID3, "RO" as "RO4", IF("RO"='O',"ID of related object") as "Org.ID4", IF("RO"='S',"ID of related object") as "Pos.ID4" FROM * (txt, codepage is 28591, embedded labels, delimiter is ',', msq); Outer Join Concat: Normal_Relation5: LOAD ObjectID as Org.ID4, "RO" as "RO5", IF("RO"='O',"ID of related object") as "Org.ID5", IF("RO"='S',"ID of related object") as "Pos.ID5" FROM * (txt, codepage is 28591, embedded labels, delimiter is ',', msq); Outer Join Concat: Normal_Relation6: LOAD ObjectID as Org.ID5, "RO" as "RO6", IF("RO"='O',"ID of related object") as "Org.ID6", IF("RO"='S',"ID of related object") as "Pos.ID6" FROM * (txt, codepage is 28591, embedded labels, delimiter is ',', msq); Outer Join Concat: Normal_Relation7: LOAD ObjectID as Org.ID6, "RO" as "RO7", IF("RO"='O',"ID of related object") as "Org.ID7", IF("RO"='S',"ID of related object") as "Pos.ID7" FROM * (txt, codepage is 28591, embedded labels, delimiter is ',', msq); Final_Access: NoConcatenate Load*, If([Pos.ID1]=[Pos.ID1], [Pos.ID1], If([Pos.ID2]=[Pos.ID2], [Pos.ID2], If([Pos.ID3]=[Pos.ID3], [Pos.ID3], If([Pos.ID4]=[Pos.ID4], [Pos.ID4], If([Pos.ID5]=[Pos.ID5], [Pos.ID5], If([Pos.ID6]=[Pos.ID6], [Pos.ID6], If([Pos.ID7]=[Pos.ID7], [Pos.ID7], IF("Pos.IDZ-Rel","Pos.IDZ-Rel")))))))) as POSITIONID ; Load*, If(USERID='DOMAIN\USER1','*',
If(USERID='DOMAIN\USER2','*',
PREPOSITIONID)) as POSITIONID; Load*, IF(USERID='DOMAIN\USER1','ADMIN',
IF(USERID='DOMAIN\USER2','ADMIN',
'USER')) as ACCESS; Load USERID, "PERSONALID", RO, HRPOSITIONID, "ACCES(TO)", RO1, Org.ID1, Pos.ID1, "RO2", "Org.ID2", "Pos.ID2", "RO3", "Org.ID3", "Pos.ID3", "RO4", "Org.ID4", "Pos.ID4", "RO5", "Org.ID5", "Pos.ID5", "RO6", "Org.ID6", "Pos.ID6", "RO7", "Org.ID7", "Pos.ID7", "Pos.IDZ-Rel" Resident Concat; Drop Table Concat;
Section Access:
Star is *; Section Access; Load "ACCESS", "USERID", "POSITIONID" Resident FINAL; Section Application;
Hi
Please check a few things
1: Have you added the system user account to the section access
2: Everything in the section access table should be in upper case.
3: Are your access properly defined.
4: I can't find the Final table you have taken resident of j the section access part, please check that.
-Pradosh
Hi Pradosh,
Thank you for your reply.
1. Good point, I added the now, but no change.
2. Yes, got that.
3. Accesses are properly defined
4. Sorry, for Final I use "Final_Access", I forgot to change this.
It would be hugely appreciated if you might be able to help me here.
did you change the position id to upper(position id ) in the section access table and the table which has the position id as well? remember Qlik is case sensitive to you have to change them using upper at both places.
It should work unless i am missing something .