Set Analysis?

[#1]

Hi

I am having difficulty in presenting data from a file which includes 3 staff roles.  My input file is a list of reporting activity and contains staff identifiers in fields as REPORTER, SECONDARY REPORTER or VERIFIER.  Therefore the member of staff may appear as any or all of these roles.#

I want to produce a pivot table showing the number of times each member of staff appears in each of these roles and therefore need a list of all the staff codes as a dimension.  I have created a second table, which is not associated, which pulls out all of the staff identifiers from the first file as Staff_Code, whichever role the staff member has fulfilled so as to create a list of all staff.  If I use this Staff_Code field as my dimension how would I count the number of times each member of staff's Staff_Code appears as REPORTER from the table created by my input file.. I tried creating link tables of REPORTER, SECONDARY REPORTER or VERIFIER between the first table and my staff list table but this created loops and therefore 'loose joins'.

I am unsure as to whether I need to create other associations of data or can use set analysis to present this data?

[tresesco]

Could have been easier for us here if you could provide a more about your data model(table details). If you have all staff type transactions in a single table. Try like:

Dim: Staff_Code

Exp1: Count({<[staff identifiers field={'REPORTER'}>} Staff_Code)            // for count as REPORTER

Exp2: Count({<[staff identifiers field={'SECONDARY REPORTER'}>} Staff_Code)    // for count as SECONDARY REPORTER

Exp3: ....similarly

[#3]

I'm sorry the base table contains:

Report No. Report Date   REPORTER     SECONDARY REPORTER   VERIFIER      additional fields.

1               09/05/15       SI1234                                                       SI5678

2               09/05/15       SI5678              SI1234

3               09/05/15       SI0987

4               09/05/15       SI1234              SI0987                               SI5678

5              10/05/15        SI1234              SI0987                               SI5678

6              10/05/15        SI5678                                                       SI1234

7              10/05/15        SI5678                                                       SI5555

I have created a second table of staff details

Staff_Code  Initials  Role

SI1234        AB       Manager

SI5678        FN       HR    

SI0987        HD       Admin

SI5555        SA       Admin

I can't use REPORTER, SECONDARY REPORTER or VERIFIER as the dimension on the table as staff do not necessarily appear in each ie SI5555 only appears listed as VERIFIER.  Therefore I need a dimension which includes a distinct value for each and every staff ID but there is no association between the tables. My pivot should appear as

Staff_Code               No. Reporter           No. SECONDARY REPORTER     No Verified

SI1234                           3                                        1                                    1

SI5678                           3                                        0                                    3     

SI0987                           1                                        2                                    0

SI5555                           0                                        0                                    1

I hope this clarifies.  I had come up with set analysis similar to the previous answer (and also tried the suggestion) but this is returning the same number  for each staff member.

Thanks

[#4]

I'm sorry the base table contains:

Report No. Report Date   REPORTER     SECONDARY REPORTER   VERIFIER      additional fields.

1               09/05/15       SI1234                                                       SI5678

2               09/05/15       SI5678              SI1234

3               09/05/15       SI0987

4               09/05/15       SI1234              SI0987                               SI5678

5              10/05/15        SI1234              SI0987                               SI5678

6              10/05/15        SI5678                                                       SI1234

7              10/05/15        SI5678                                                       SI5555

I have created a second table of staff details

Staff_Code  Initials  Role

SI1234        AB       Manager

SI5678        FN       HR   

SI0987        HD       Admin

SI5555        SA       Admin

I can't use REPORTER, SECONDARY REPORTER or VERIFIER as the dimension on the table as staff do not necessarily appear in each ie SI5555 only appears listed as VERIFIER.  Therefore I need a dimension which includes a distinct value for each and every staff ID but there is no association between the tables. My pivot should appear as

Staff_Code               No. Reporter           No. SECONDARY REPORTER     No Verified

SI1234                           3                                        1                                    1

SI5678                           3                                        0                                    3    

SI0987                           1                                        2                                    0

SI5555                           0                                        0                                    1

I hope this clarifies.  I had come up with set analysis similar to the previous answer (and also tried the suggestion) but this is returning the same number  for each staff member.

Thanks

[Anonymous]

Try a straight table with one dimension Staff_Code, and three expressions:

count(if(Staff_Code=REPORTER, Staff_Code))

count(if(Staff_Code="SECONDARY REPORTER", Staff_Code))

count(if(Staff_Code=VERIFIER, Staff_Code))

[#6]

Unfortunately I can't use a straight table as I want other factors in ie the report date, the type of report to give totals per month.

[Anonymous]

Not enough information.  Do you have a specific layout of you table in mind?

[#8]

Hi Michael

Thanks for your interest.  But on further testing the script you suggested appears to be bring back meaningful results on my pivot.. Will need more testing for full confidence but at least I can progress

Many thanks