topic multiple ID's for one person in QlikView

multiple ID's for one person

Tue, 20 Jul 2010 19:11:32 GMT

Hi,

I have an interesting scenario, hoping someone can give me some guidance...

I have a table of sales transaction. Each transaction can have a salesman1 and a salesman2. When I want to do an analysis of John Smith, I want the results to show all sales he was involved in whether he was labelled as salesman1 or salesman2 on the transaction. The salesman1/2 are ID's not names. Currently I have a seperate table linking all the ID's of salesman1 to a name.

In addition, the sales amount attributed to salesman1 and 2 differ on each transaction.

I hope this was clear....

multiple ID's for one person

Tue, 20 Jul 2010 21:03:56 GMT

To be more clear...

Every salesman has a number, on any transaction, that number can be associated in the SLM1 field or the SLM2 field.

When a transaction is completed, a percentage of the sales dollars is credited to SLM 1 and the rest to SLM2. This percentage can change on each transaction.

When I want to do an analysis of a specific salesman, I want qlikview to pull the sales dollar when the saleman is assocaited with SLM 1 and the sales dolalrs when the salesman is associated with SLM2. To get a total amount associated with the salesman.

If my statement were to be SUM(sales) SLM1 and SLM2 where SLM1 = SLM2 I would get zero as SLM1 will never = SLM2 on a given transaction.

Does anyone have any suggestions?

multiple ID's for one person

Wed, 21 Jul 2010 13:37:13 GMT

Would be easier if you could upload some data for the problem

multiple ID's for one person

Wed, 21 Jul 2010 13:49:07 GMT

Order	Customer	SLM 1	SLM 2	SLM 1 Sales	SLM 2 Sales	Total Sales
1	ABC	1	6	90	10	100
2	ABC	2		150		150
3	FE inc	3	4	30	20	50
4	Light	4	6	25	10	35
5	Light	5	6	65	15	80
6	Qink	4	5	80	5	85
7	Sutton	3		10		10
8	Sutton	6	1	45	30	75

That is an example of my transaction file, and this would be a table attached linking salesman numbers to salesman names

Salesman	Salesman #
John	1
Pierre	2
Bob	3
Cara	4
Lisa	5
David	6

Now when I do a search for "David" i want all the sales dollars to come up for him.

This is what my actual tables look like....

multiple ID's for one person

Wed, 21 Jul 2010 14:00:25 GMT

So in case of David the SLM 1 Sales = 45 and SLM 2 Sales = 35 right ....

You could create a table as under

Salesman as Dimension

Expression for SLM1Sales = sum ( if ( [Salesman #] = [SLM 1] , [SLM 1 Sales] , ''))

Expression for SLM2Sales = sum ( if ( [Salesman #] = [SLM 2] , [SLM 2 Sales] , ''))

multiple ID's for one person

Wed, 21 Jul 2010 14:05:55 GMT

Would this be in my script? How would I go about linking salesman names to the slm1 and slm2 numbers without creating a loop?

multiple ID's for one person

Wed, 21 Jul 2010 14:15:08 GMT

This is not in the script it's just a table created in QV ... I've attache an example file you can have a look

multiple ID's for one person

Wed, 21 Jul 2010 14:29:12 GMT

Thanks for all the help, I think I can make this work with my situation!