topic Re: Double dimension in QlikView

Double dimension

Fri, 20 Dec 2013 14:29:26 GMT

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

Re: Double dimension

Fri, 20 Dec 2013 14:35:13 GMT

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.

Re: Double dimension

hic — Fri, 20 Dec 2013 14:36:40 GMT

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

Re: Double dimension

swuehl — Fri, 20 Dec 2013 14:40:28 GMT

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)

Re: Double dimension

Fri, 20 Dec 2013 14:49:52 GMT

Thanks, swuehl.

That worked perfectly.

Thanks to other too ... all helpful.

Kindest...

Re: Double dimension

Fri, 20 Dec 2013 16:03:19 GMT

Great Idea...