Table connections and if statements

[#1]

Hello!

Wondering if someone can help me here. My application is built to analyze sales transactions. All of my data is brought in from one large excel file, and then connected to smaller excel files.

Here is my issue: Each of my transactions have two salesman. Labelled as SLM-1 and SLM-2. If "John" is involved with the sale, his salesman # can appear in either the SLM-1 field or the SLM-2 field. The sales amount on each transaction is also split (not evenly) through the fields SLM-1 Amount and SLM-2 Amount. ALL of these fields are in one table.

If I want to do an analysis of John's sales, I would have to add up all the SLM-1 Amount where John is SLM-1 AND all the SLM-2 Amount, where John is SLM-2.

Right now I have created a table that is not connected to everything else that has each salesman name (NAME) and there salesman # (SLM). To do my analysis, on the frontend of my charts expressions, I was using =sum ( if ( SLM = [SLM-1] , [SLM-1 AMT] , '')) + sum ( if ( SLM = [SLM-2] , [SLM-2 AMT] , ''))

This works however I have just purchased document licenses and they do not work when you have unconnected tables. So does anyone have an idea for how I can make this work?

[#2]

this is my table view

[Anonymous]

Les,
When I had a situation with split sales, I used different approach. Each Sales record is split into two records in QV. They were identical except two fields - Salesman and Amount. For example, if there is one sales record for John and Peter in database, it is two records in QV, say John with sales amount 60 and Peter with sales amount 40.
A little bit complex in the script, but no pain on the front end, just regular sum(Sales).

I think it should be rather simple for you in the script since you don't have to calcualte splits. Just load
LOAD DISTINCT
...
"SLM-1" as SLM,
"SLM-1 Amount" as Amount
(and don't load SLM-2 fields)
...
RESIDENT Data;

CONCATENATE
LOAD DISTINCT
...
"SLM-2" as SLM,
"SLM-2 Amount" as Amount
(and don't load SLM-1 fields)
...
RESIDENT Data;

[pover]

The best option would be to load the sales transaction table twice and concatenate them. The first load with "SLM-1 as SLM" and "[SLM-1 Amount] as Amount" and the second load with "SLM-2 as SLM" and "[SLM-1 Amount] as Amount". This will work great if you don't have another field that is an amount. If you have another field that is an amount you could pro-rate the amount (Eg. Cost) between the two salespersons. This method also makes for much simpler and faster expressions in the GUI.

Regards.

[#5]

Thanks for the reply Michael,

I will try this out !

[Anonymous]

Exactly 🙂

[Anonymous]

I just edited my response, hope it helps

[pover]

Beaten to the punch. I should have paid more attention in my keyboarding class...

[Anonymous]

Karl,
Wasn't my intention. You were on the right path anyway.

[#10]

COMMENT REMOVED,

Found fix