7 Replies Latest reply: Jul 21, 2010 10:29 AM by Les Backman RSS

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

        • 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?