Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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

View solution in original post

7 Replies
Not applicable
Author

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
Author

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

Not applicable
Author

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
Author

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
Author

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
Author

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
Author

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