topic Re: Per Person Data in QlikView

Per Person Data

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Good Afternoon;

I have a need to split data on one dashboard between three people.

Example. I have three supervisors who manage different individuals. Upon logging into the dashboard i need them to see only the information related to those individuals they supervise.

How can this be accomplished?

Thank you in advance

Rich

Re: Per Person Data

eduardo_dimperio — Tue, 07 Aug 2018 17:33:09 GMT

You can use a this:

and create an expression based on user logged in

Re: Per Person Data

MK9885 — Tue, 07 Aug 2018 17:37:28 GMT

Use Section Access based on the field you want to restrict and their NT Names

QlikView Section Access Examples

Re: Per Person Data

Anonymous — Tue, 07 Aug 2018 17:54:54 GMT

Could you explain a little more, I have not seen that in Qlikview.

Re: Per Person Data

Anonymous — Tue, 07 Aug 2018 18:07:09 GMT

Hello Khan;

I have looked at the document, I think i understand, I have ACCESS, NTNAME, NTDOMAINSID.

Do i need to define the Admins as well? Also the one thing i didnt see is how to set user 1 to group A of data, user 2 to group B, etc. is that something different?

Re: Per Person Data

MK9885 — Tue, 07 Aug 2018 18:15:38 GMT

Admin will be your ID where you give '*' in the field value... which would allow you to see all the fields/data in app.

How many users do you have in Group 1 and Group 2?

One way to do it is either create a 2 new AD groups in your AD group (request your help desk)

or manually enter the user names by assigning what fields they should see

You can use OMIt to restrict it by Colum and for Row just use the normal field name in Upper() for both fact field and your SA script.

If you can post a list of sample user & what you want them to be restricted then it would be easier to create a mock up script.

Remember: Always make a copy before applying section access so that you won't get locked out.

If it is used in Qlik Sense then right Qlik on app, open without data would work but Qlik View will not.. so make a copy of the app.

Re: Per Person Data

eduardo_dimperio — Tue, 07 Aug 2018 18:22:34 GMT

To be honest thos field exist on Qlik Sense, but i think that my be a similar in QV. It used to show a information only if that expression is satisfied

Re: Per Person Data

Anonymous — Tue, 07 Aug 2018 19:04:32 GMT

HI Khan;

Here is what I have, i thought i got it, but the document fails to publish to the access point so I assume i have something wrong

Section Access;

LOAD * INLINE [

ACCESS, NTNAME, DIVISION

ADMIN, Domain\user1,

USER, Domain\user2, ATC

USER, Domain\user3, WBO

USER, Domain\user4, VCE

];

Section Application;

I'm not sure what I am doing wrong i got it from example 6 and i made sure to go into the document settings. Do i have something out of place?

Re: Per Person Data

vishsaggi — Tue, 07 Aug 2018 19:31:00 GMT

What is the error you are getting? Is the field Division in your table all UPPER case? I mean outside section access you have a table with Field Division whose values should also be in upper case.

Re: Per Person Data

MK9885 — Tue, 07 Aug 2018 19:37:30 GMT

Star is *;

Section Access;

LOAD * INLINE [

ACCESS, NTNAME, DIVISION

ADMIN, Domain\user1, *

USER, Domain\user2, ATC

USER, Domain\user3, WBO

USER, Domain\user4, VCE

];

Section Application;

Make sure your Division field in fact or dimension table is in upper

Ex: rename the field

Upper(DIVISION) as DIVISION

Re: Per Person Data

Anonymous — Tue, 07 Aug 2018 19:57:04 GMT

no error, it just wont publish to access point, just says failed. The field name is actually "Target"

GroupMap2:

MAPPING LOAD * INLINE [

Source, Target

I have renamed it now though. for some reason if i take the section access it publishes, but when i put it in it fails. Could i be missing something else?

Re: Per Person Data

vishsaggi — Tue, 07 Aug 2018 20:04:42 GMT

Sorry did not get you. What is this mapping table for? And you said Target is the field what is this field you mean this is matching to Division? If possible can you share your script please to look into?

Re: Per Person Data

Anonymous — Tue, 07 Aug 2018 20:19:10 GMT

Thank you for any help you can provide.

I don't get a specific error just when I'm trying to publish it fails, seems to reload just fine

Please see below;

Section Access;

LOAD * INLINE [

ACCESS, NTNAME, TARGET

ADMIN, vcehqs\user 1, *

ADMIN, vcehqs\user 2, *

USER, vcehqs\user 3, WBO

USER, vcehqs\user 4, VCE

USER, vcehqs\user 5, ATC

USER, wbo\user 6, WBO

USER, vce\user 7, VCE

];

Section Application;

$(Include=..\..\setup\connection.qvs);

$(Include=..\..\..\framework\common\connection-cp.qvs);

HCOrgMap:

MAPPING LOAD

HR_ORG_ID,

HR_ORG_DESC;

SELECT * FROM DELTEK.HR_ORG_MGR_DFLT;

HCAgingTable2:

LOAD * INLINE [

FromAge2, ToAge2, AgeRange2, AgeDesc2

72.9, 100, Traditionalists, born before 1946

53, 72.899, Baby Boomers, born 1946 to 1964

41, 52.9999, Generation X, born 1965 to 1976

21, 40.999, Generation Y, born 1977 to 1997

0, 20.999, Generation Z, born after 1997

];

HCAgingTable:

LOAD * INLINE [

FromAge, ToAge, AgeRange

0, 29, 0-29

30, 39, 30-39

40, 49, 40-49

50, 59, 50-59

60, 100, 60 +

];

GroupMap2:

MAPPING LOAD * INLINE [

Source, Target

1 ,ATC

2 ,ATC

3 ,ATC

4 ,WBO

5 ,WBO

6 ,ATC

7 ,VCO

8 ,VCO

9 ,ATC

10 ,ATC

11 ,VCE

12 ,ATC

13 ,WBO

14 ,WBO

15 ,VCE

];

HCEMPL:

LOAD* where HCYear>=2013;

LOAD *,

Year(HCHireDate+iterno()-1) as HCYear,

Month(HCHireDate+iterno()-1) as HCMonth,

NUM(Month(HCHireDate+iterno()-1)) as HCMonthNo,

Day(HCHireDate+iterno()-1) as HCDay,

Date(HCHireDate+iterno()-1) as HCDate,

1 as HCCount

while (date(HCHireDate)+iterno()-1) <= date(HCTERM_DT);

LOAD

HCID

,HCStatus

,HCGroup

,HCEntity

,HCHireDate

,HCTenure

,HCBirthDT

,HCAge

,HCGender

,HCRace

,HCTERM_DT

,HCLastName

,HCFirstName

,HCLastName &', '& HCFirstName as HCName;

//WHERE match(HCEntity, 'ICRC', 'VSS', 'BVA')=0;

LOAD

TEXT(EMPL_ID) as HCID,

date(BIRTH_DT) as HCBirthDT,

//age(now(), BIRTH_DT) as HCAge,

//age(now(), ORIG_HIRE_DT) as HCTenure,

Num((fabs(Date(now(),'DD/MM/YYYY')) - fabs(Date(BIRTH_DT,'DD/MM/YYYY')))/365, '#,##0.00')as HCAge,

(fabs(Date(now(),'DD/MM/YYYY')) - fabs(Date(ORIG_HIRE_DT,'DD/MM/YYYY')))/365 as HCTenure,

SEX_CD as HCGender,

S_RACE_CD as HCRace,

//trim(ApplyMap ('DivisionMap1', upper("TAXBLE_ENTITY_ID"), upper("TAXBLE_ENTITY_ID"))) as [HCDivision],

trim(ApplyMap ('GroupMap2', upper("TAXBLE_ENTITY_ID"), upper("TAXBLE_ENTITY_ID"))) as [HCGroup],

if(TAXBLE_ENTITY_ID='1', 'GLOBAL', if(TAXBLE_ENTITY_ID='ULTRA', 'WHEELER', TAXBLE_ENTITY_ID)) as HCEntity,

//if(TAXBLE_ENTITY_ID='1', 'GLOBAL', TAXBLE_ENTITY_ID) as HCEntity,

//SSN_ID,

date(ORIG_HIRE_DT) as HCHireDate,

S_EMPL_STATUS_CD as HCStatus,

FIRST_NAME as [HCFirstName],

LAST_NAME as [HCLastName],

//ADJ_HIRE_DT,

//Year (TERM_DT) as TYear,

//Month (TERM_DT) as TMonth,

//num(Month (TERM_DT)) as [Pd No],

if(isnull(TERM_DT), today(), date(TERM_DT)) as HCTERM_DT;

LOAD *

FROM data\qvd\EMPL.qvd(qvd) where S_EMPL_STATUS_CD='ACT' and left(EMPL_ID, 2)<>'CE' AND NOT ISNULL(EMPL_ID);

JOIN

IntervalMatch ( HCAge )

LOAD FromAge, ToAge RESIDENT HCAgingTable;

JOIN

IntervalMatch ( HCAge )

LOAD FromAge2, ToAge2 RESIDENT HCAgingTable2;

Re: Per Person Data

MK9885 — Tue, 07 Aug 2018 20:50:05 GMT

Load Section Access Script after all other script...

Where is the Field named TARGET in your data model?

I do not see it in the script....

If I'm not wrong It should rather be HCGROUP

$(Include=..\..\setup\connection.qvs);

$(Include=..\..\..\framework\common\connection-cp.qvs);

HCOrgMap:

MAPPING LOAD

     HR_ORG_ID,

     HR_ORG_DESC;

SELECT * FROM DELTEK.HR_ORG_MGR_DFLT;

HCAgingTable2:

LOAD * INLINE [

     FromAge2, ToAge2, AgeRange2, AgeDesc2

     72.9, 100, Traditionalists, born before 1946

     53, 72.899, Baby Boomers,   born 1946 to 1964

     41, 52.9999, Generation X, born 1965 to 1976

     21, 40.999, Generation Y,   born 1977 to 1997

     0, 20.999, Generation Z,    born after 1997

];

HCAgingTable:

LOAD * INLINE [

FromAge, ToAge, AgeRange

     0, 29, 0-29

     30, 39, 30-39

     40, 49, 40-49

     50, 59, 50-59

     60, 100, 60 +

];

GroupMap2:

MAPPING LOAD * INLINE [

Source, Target

     1 ,ATC

     2 ,ATC

     3 ,ATC

     4 ,WBO

     5 ,WBO

     6 ,ATC

     7 ,VCO

     8 ,VCO

     9 ,ATC

     10 ,ATC

     11 ,VCE

     12 ,ATC

     13      ,WBO

     14      ,WBO

     15      ,VCE

];

HCEMPL:

LOAD* where HCYear>=2013;

LOAD *,

Year(HCHireDate+iterno()-1) as HCYear,

Month(HCHireDate+iterno()-1) as HCMonth,

NUM(Month(HCHireDate+iterno()-1)) as HCMonthNo,

Day(HCHireDate+iterno()-1) as HCDay,

Date(HCHireDate+iterno()-1) as HCDate,

1 as HCCount

while (date(HCHireDate)+iterno()-1) <= date(HCTERM_DT);

LOAD

HCID

,HCStatus

, Upper(HCGroup) as HCGROUP

,HCEntity

,HCHireDate

,HCTenure

,HCBirthDT

,HCAge

,HCGender

,HCRace

,HCTERM_DT

,HCLastName

,HCFirstName

,HCLastName &', '& HCFirstName as HCName;

//WHERE match(HCEntity, 'ICRC', 'VSS', 'BVA')=0;

LOAD

TEXT(EMPL_ID) as HCID,

date(BIRTH_DT) as HCBirthDT,

//age(now(), BIRTH_DT) as HCAge,

//age(now(), ORIG_HIRE_DT) as HCTenure,

Num((fabs(Date(now(),'DD/MM/YYYY')) - fabs(Date(BIRTH_DT,'DD/MM/YYYY')))/365, '#,##0.00')as HCAge,

(fabs(Date(now(),'DD/MM/YYYY')) - fabs(Date(ORIG_HIRE_DT,'DD/MM/YYYY')))/365 as HCTenure,

SEX_CD as HCGender,

S_RACE_CD as HCRace,

//trim(ApplyMap ('DivisionMap1', upper("TAXBLE_ENTITY_ID"), upper("TAXBLE_ENTITY_ID"))) as [HCDivision],

trim(ApplyMap ('GroupMap2', upper("TAXBLE_ENTITY_ID"), upper("TAXBLE_ENTITY_ID"))) as [HCGroup],

if(TAXBLE_ENTITY_ID='1', 'GLOBAL', if(TAXBLE_ENTITY_ID='ULTRA', 'WHEELER', TAXBLE_ENTITY_ID)) as HCEntity,

//if(TAXBLE_ENTITY_ID='1', 'GLOBAL', TAXBLE_ENTITY_ID) as HCEntity,

//SSN_ID,

date(ORIG_HIRE_DT) as HCHireDate,

S_EMPL_STATUS_CD as HCStatus,

FIRST_NAME as [HCFirstName],

LAST_NAME as [HCLastName],

//ADJ_HIRE_DT,

//Year (TERM_DT) as TYear,

//Month (TERM_DT) as TMonth,

//num(Month (TERM_DT)) as [Pd No],

if(isnull(TERM_DT), today(), date(TERM_DT)) as HCTERM_DT;

LOAD *

FROM data\qvd\EMPL.qvd(qvd) where S_EMPL_STATUS_CD='ACT' and left(EMPL_ID, 2)<>'CE' AND NOT ISNULL(EMPL_ID);

JOIN

IntervalMatch ( HCAge )

LOAD FromAge, ToAge RESIDENT HCAgingTable;

JOIN

IntervalMatch ( HCAge )

LOAD FromAge2, ToAge2 RESIDENT HCAgingTable2;

Star is *;

Section Access;

LOAD * INLINE [

    ACCESS, NTNAME, HCGROUP

    ADMIN, vcehqs\user 1, *

    ADMIN, vcehqs\user 2, *

    USER, vcehqs\user 3, WBO

    USER, vcehqs\user 4, VCE

    USER, vcehqs\user 5, ATC

    USER, wbo\user 6, WBO

    USER, vce\user 7, VCE

];

Section Application;

Try now?

Re: Per Person Data

vishsaggi — Tue, 07 Aug 2018 20:56:21 GMT

I believe you cannot use Target as it is a field from mapping table. Coz mapping table will be dropped automatically after the end of the script. So use like below and try?

Section Access;

LOAD * INLINE [

ACCESS, NTNAME, HCGroup

ADMIN, vcehqs\user 1, *

ADMIN, vcehqs\user 2, *

USER, vcehqs\user 3, WBO

USER, vcehqs\user 4, VCE

USER, vcehqs\user 5, ATC

USER, wbo\user 6, WBO

USER, vce\user 7, VCE

];

Section Application;

And before you publish on to QMC please check Generate log file check box from Document properties of your qvw file. So that if you have any errors it will create a document log.

Re: Per Person Data

Anonymous — Tue, 07 Aug 2018 21:44:27 GMT

Thank you, I was able to get it to load, however it still wont publish to the access point. I got a log file and cant see any issue. All i see is the screen shot attached. I went to the location of the log files for the server and cant see anything helpful there, i am completely stuck.

Re: Per Person Data

MK9885 — Tue, 07 Aug 2018 21:51:21 GMT

LOAD

HCID

,HCStatus

, Upper(HCGroup) as HCGROUP

.............

in your preceding load

and finally at the end of the script, not in first...

Star is *;

Section Access;

LOAD * INLINE [

ACCESS, NTNAME, HCGROUP

ADMIN, vcehqs\user 1, *

ADMIN, vcehqs\user 2, *

USER, vcehqs\user 3, WBO

USER, vcehqs\user 4, VCE

USER, vcehqs\user 5, ATC

USER, wbo\user 6, WBO

USER, vce\user 7, VCE

];

Section Application;

Re: Per Person Data

vishsaggi — Wed, 08 Aug 2018 00:12:32 GMT

IF you are not one of the users in ADMIN groups then it will not distribute to you. You should add your login to SECTION ACCESS in order to view that dashboard in Access Point.