Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

multiple ID's for one person

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

1 Solution

Accepted Solutions
Not applicable

multiple ID's for one person

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

7 Replies
Not applicable

multiple ID's for one person

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?

Not applicable

multiple ID's for one person

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

Not applicable

multiple ID's for one person

Order

CustomerSLM 1SLM 2SLM 1 SalesSLM 2 SalesTotal Sales
1ABC169010100
2ABC2150150
3FE inc34302050
4Light46251035
5Light56651580
6Qink4580585
7Sutton31010
8Sutton61453075

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

SalesmanSalesman #
John1
Pierre2
Bob3
Cara4
Lisa5
David6


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

Not applicable

multiple ID's for one person

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] , ''))

Not applicable

multiple ID's for one person

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

Not applicable

multiple ID's for one person

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

Not applicable

multiple ID's for one person

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