Double dimension

[#1]

Hi,

I have a table that has a number of fields. Two of the fields have similar possible values. I need to do a count on the values of the two similar fields

An example:

Region	Seller	Assistant	Sale
US	Bob	Bill	10
UK	Bill	Tom	15
UK	Bob	Tom	5
US	Tom	Bob	20
UK	Bill	Bob	5

I want to find out the COUNT of Sale for each person, regardless if they were the Seller or Assistant. They cannot be both for the same transaction.

The result set would be:

Seller/Assistant	Count of Sale
Bill	3
Bob	4
Tom	3

How can I achieve this?

Thanks...

[#2]

If you want an overview of sales per person you can load both Seller and Assistant in one new column at your load script?

Or simply add up the values. And create a new dimension, but the load will keep your front end faster.

[hic]

You should create a new dimension in your data model. If your first table is the TransactionTable, you could do this with

Person:

Load Seller as 'Seller/Assistant', TransactionID resident TransactionTable;

Load Assistant as 'Seller/Assistant', TransactionID resident TransactionTable;

HIC

[swuehl · Accepted solution]

Maybe like this:

INPUT:

LOAD Region,

     Seller,

     Assistant,

     Sale,

     recno() as SaleID

FROM

[http://community.qlik.com/thread/102498]

(html, codepage is 1252, embedded labels, table is @1);

SellerAssistant:

LOAD SaleID, Seller as SellerAssistant

Resident INPUT;

LOAD SaleID, Assistant as SellerAssistant

Resident INPUT;

Then create a chart with dimension SellerAssistant and expression =count(Sale)

[#5]

Thanks, swuehl.

That worked perfectly.

Thanks to other too ... all helpful.

Kindest...

[#6]

Great Idea...