Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have spent 3 days on the following problem but not enough luck in finding the correct answer. I have the following table as my source table.
TABLE:
Opps | Owner | Value | Team |
A1 | Tony | 2500 | Tony;Bob;Helen |
A2 | Tony | 10000 | Bob,George,Tony |
A3 | Tony | 12000 | Helen;Tony |
A4 | Tony | 45000 | Tony |
A5 | Tony | 30000 | Tony |
A6 | Tony | 30000 | George;Tony |
A7 | Tony | 50000 | Tony |
A12 | Steve | 12000 | Tony;Mike;Balao;Helen |
A13 | Miara | 45000 | George;Mike;Helen |
A15 | Bob | 30000 | Bob,George,Tony |
A16 | Tony | 50000 | Sunny;Tony |
A17 | Sunny | 30000 | George;Mike;Helen |
A18 | Tony | 30000 |
What i want to do is the following:
1: I want to do set analysis on the above table and make variables vTeam and vRep.
2: for vTeam, I want to sum values where a person exists in the Team column (NOT ON ITS OWN BUT IN A GROUP) but not in the Owner column. So for example if we took Tony as an example, his resulting table would like this and i only want the sum (in RED) value and not the table itself.
Opps | Owner | Value | Team |
A12 | Steve | 12000 | Tony;Mike;Balao;Helen |
A15 | Bob | 30000 | Bob,George,Tony |
42000 |
3: For vRep, I want to sum values where a person exists in the Owner column and in the Team column so for example if we took Tony again, his table would look like the following, i only want the sum and not the actual table:
Opps | Owner | Value | Team |
A1 | Tony | 2500 | Tony;Bob;Helen |
A2 | Tony | 10000 | Bob,George,Tony |
A3 | Tony | 12000 | Helen;Tony |
A4 | Tony | 45000 | Tony |
A5 | Tony | 30000 | Tony |
A6 | Tony | 30000 | George;Tony |
A7 | Tony | 50000 | Tony |
A16 | Tony | 50000 | Sunny;Tony |
A18 | Tony | 30000 | |
259500 |
UPDATE:
This is the results im looking for :
So, if i select tony, the value should sum up (where he exists in Owner and Team)
Final Table: | |
Tony | 301500 |
Steve | 12000 |
Miara | 45000 |
Bob | 42500 |
Sunny | 80000 |
Side Note:
The reason i want this to be a set analysis is because i have this information in a pivot table along with targets and actuals. So, if i select tony from there, target and actuals show fine. Just the opps value is wrong. I was hoping to sum vRep+ vTeam to get the total value in that table.
Please let me know if you need more information.
Thank you
Check this.
tab1:
LOAD *, If(SubField(Team_Name,';')<>Owner, SubField(Team_Name,';')) As vTeam
;
LOAD *, Replace(Team, Owner, '') As Team_Name
, If(Len(Team)=0 Or Index(Team,Owner), Owner) As vRep
;
LOAD RecNo() As RecID, * INLINE [
Opps, Owner, Value, Team
A1, Tony, 2500, Tony;Bob;Helen
A2, Tony, 10000, Bob;George;Tony
A3, Tony, 12000, Helen;Tony
A4, Tony, 45000, Tony
A5, Tony, 30000, Tony
A6, Tony, 30000, George;Tony
A7, Tony, 50000, Tony
A12, Steve, 12000, Tony;Mike;Balao;Helen
A13, Miara, 45000, George;Mike;Helen
A15, Bob, 30000, Bob;George;Tony
A16, Tony, 50000, Sunny;Tony
A17, Sunny, 30000, George;Mike;Helen
A18, Tony, 30000,
];
tab2:
LOAD DISTINCT vTeam As Owner, Value As Team_Value, Opps As Team_Opps
Resident tab1;
LOAD DISTINCT vRep As Owner, Value As Rep_Value, Opps As Rep_Opps
Resident tab1;
Drop Table tab1;
A3 and A6 in the vRep table are incorrect. Can you check?
Hi,
Yes, it has been corrected. The main table was changed to represent the correct results.
Thanks
One solution in Script:
tab1:
LOAD *, SubField(Team_Name,';') As vTeam
;
LOAD *, Replace(Team, Owner, '') As Team_Name
, If(Len(Team)=0 Or Index(Team,Owner), Owner) As vRep
;
LOAD RecNo() As RecID, * INLINE [
Opps, Owner, Value, Team
A1, Tony, 2500, Tony;Bob;Helen
A2, Tony, 10000, Bob;George;Tony
A3, Tony, 12000, Helen;Tony
A4, Tony, 45000, Tony
A5, Tony, 30000, Tony
A6, Tony, 30000, George;Tony
A7, Tony, 50000, Tony
A12, Steve, 12000, Tony;Mike;Balao;Helen
A13, Miara, 45000, George;Mike;Helen
A15, Bob, 30000, Bob;George;Tony
A16, Tony, 50000, Sunny;Tony
A17, Sunny, 30000, George;Mike;Helen
A18, Tony, 30000,
];
Output
Hi,
Thank you for your time.
This is what im looking for as a result
Final Table: | |
Owner | Total Value |
Tony | 301500 |
Steve | 12000 |
Miara | 45000 |
Bob | 42500 |
Sunny | 80000 |
I want to have one field called owner with all the names in my main table. Then, when i select one person, it should sum both vTeam and vRep and give one final value. So, in case of Tony, it should be 42000+259500 = 301500.
Thanks
Check this.
tab1:
LOAD *, If(SubField(Team_Name,';')<>Owner, SubField(Team_Name,';')) As vTeam
;
LOAD *, Replace(Team, Owner, '') As Team_Name
, If(Len(Team)=0 Or Index(Team,Owner), Owner) As vRep
;
LOAD RecNo() As RecID, * INLINE [
Opps, Owner, Value, Team
A1, Tony, 2500, Tony;Bob;Helen
A2, Tony, 10000, Bob;George;Tony
A3, Tony, 12000, Helen;Tony
A4, Tony, 45000, Tony
A5, Tony, 30000, Tony
A6, Tony, 30000, George;Tony
A7, Tony, 50000, Tony
A12, Steve, 12000, Tony;Mike;Balao;Helen
A13, Miara, 45000, George;Mike;Helen
A15, Bob, 30000, Bob;George;Tony
A16, Tony, 50000, Sunny;Tony
A17, Sunny, 30000, George;Mike;Helen
A18, Tony, 30000,
];
tab2:
LOAD DISTINCT vTeam As Owner, Value As Team_Value, Opps As Team_Opps
Resident tab1;
LOAD DISTINCT vRep As Owner, Value As Rep_Value, Opps As Rep_Opps
Resident tab1;
Drop Table tab1;
Hi ,
Thank you for replying so quickly.
You solution is almost correct. Some mistakes:
1: Sunny is missing 30000 from his Values.
2: its missing values for Miara and Steve completely.
Hello,
the issue is solved if you replace the following:
If(Len(Team)=0 Or Index(Team,Owner), Owner) As vRep
with :
Owner As vRep
Thanks