topic Re: Wildmatching a value in a different column in App Development

Wildmatching a value in a different column

waleeed_mahmood — Sat, 16 Nov 2024 02:07:33 GMT

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

@sunny_talwar

Re: Wildmatching a value in a different column

Saravanan_Desingh — Wed, 17 Jun 2020 21:13:21 GMT

A3 and A6 in the vRep table are incorrect. Can you check?

Re: Wildmatching a value in a different column

waleeed_mahmood — Wed, 17 Jun 2020 21:18:39 GMT

Hi,

Yes, it has been corrected. The main table was changed to represent the correct results.

Thanks

Re: Wildmatching a value in a different column

Saravanan_Desingh — Wed, 17 Jun 2020 21:50:56 GMT

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, ];

Re: Wildmatching a value in a different column

Saravanan_Desingh — Wed, 17 Jun 2020 21:51:58 GMT

Output

Re: Wildmatching a value in a different column

waleeed_mahmood — Wed, 17 Jun 2020 23:21:33 GMT

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

Re: Wildmatching a value in a different column

Saravanan_Desingh — Wed, 17 Jun 2020 23:23:29 GMT

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;

Re: Wildmatching a value in a different column

Saravanan_Desingh — Wed, 17 Jun 2020 23:24:44 GMT

Re: Wildmatching a value in a different column

waleeed_mahmood — Wed, 17 Jun 2020 23:34:13 GMT

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.

Re: Wildmatching a value in a different column

waleeed_mahmood — Wed, 17 Jun 2020 23:44:26 GMT

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

Re: Wildmatching a value in a different column

Saravanan_Desingh — Wed, 17 Jun 2020 23:47:00 GMT

nice to hear about that

Re: Wildmatching a value in a different column

waleeed_mahmood — Wed, 17 Jun 2020 23:51:20 GMT

Thank you so much for your help @Saravanan_Desingh .

It’s really appreciated!

Re: Wildmatching a value in a different column

Saravanan_Desingh — Thu, 18 Jun 2020 02:00:04 GMT

you welcome