topic Re: Set Analysis? in QlikView

Set Analysis?

Mon, 18 May 2015 08:49:37 GMT

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?

Re: Set Analysis?

tresB — Mon, 18 May 2015 09:03:50 GMT

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

Re: Set Analysis?

Mon, 18 May 2015 10:02:34 GMT

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

Re: Set Analysis?

Mon, 18 May 2015 14:19:28 GMT

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

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

Re: Set Analysis?

Anonymous — Mon, 18 May 2015 19:29:38 GMT

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))

Re: Set Analysis?

Wed, 20 May 2015 12:38:16 GMT

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.

Re: Set Analysis?

Anonymous — Wed, 20 May 2015 15:00:58 GMT

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

Re: Set Analysis?

Wed, 20 May 2015 15:05:06 GMT

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