9 Replies Latest reply: Sep 28, 2010 2:20 PM by Les Backman

# Table connections and if statements

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?

• ###### PROPS TO WHOEVER CAN ANSWER THIS: Table connections and if statements

this is my table view

• ###### PROPS TO WHOEVER CAN ANSWER THIS: Table connections and if statements

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
...
"SLM-1" as SLM,
"SLM-1 Amount" as Amount
...
RESIDENT Data;

CONCATENATE
...
"SLM-2" as SLM,
"SLM-2 Amount" as Amount
...
RESIDENT Data;

• ###### PROPS TO WHOEVER CAN ANSWER THIS: Table connections and if statements

I will try this out !

• ###### PROPS TO WHOEVER CAN ANSWER THIS: Table connections and if statements

I just edited my response, hope it helps

• ###### PROPS TO WHOEVER CAN ANSWER THIS: Table connections and if statements

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

• ###### PROPS TO WHOEVER CAN ANSWER THIS: Table connections and if statements

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

• ###### PROPS TO WHOEVER CAN ANSWER THIS: Table connections and if statements

COMMENT REMOVED,

Found fix

• ###### Table connections and if statements

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.

• ###### Table connections and if statements

Exactly :-)