Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
waleeed_mahmood
Creator
Creator

Wildmatching a value in a different column

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:

OppsOwnerValueTeam
A1Tony2500Tony;Bob;Helen
A2Tony10000Bob,George,Tony
A3Tony12000Helen;Tony
A4Tony45000Tony
A5Tony30000Tony
A6Tony30000George;Tony
A7Tony50000Tony
A12Steve12000Tony;Mike;Balao;Helen
A13Miara45000George;Mike;Helen
A15Bob30000Bob,George,Tony
A16Tony50000Sunny;Tony
A17Sunny30000George;Mike;Helen
A18Tony30000 

 

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.

OppsOwnerValueTeam
A12Steve12000Tony;Mike;Balao;Helen
A15Bob30000Bob,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:

OppsOwnerValueTeam
A1Tony2500Tony;Bob;Helen
A2Tony10000Bob,George,Tony
A3Tony12000Helen;Tony
A4Tony45000Tony
A5Tony30000Tony
A6Tony30000George;Tony
A7Tony50000Tony
A16Tony50000Sunny;Tony
A18Tony30000 
  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:
Tony301500
Steve12000
Miara45000
Bob42500
Sunny80000

 

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 

Labels (3)
1 Solution

Accepted Solutions
Saravanan_Desingh

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;

View solution in original post

12 Replies
Saravanan_Desingh

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

waleeed_mahmood
Creator
Creator
Author

Hi,

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

Thanks

Saravanan_Desingh

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

Output

commQV38.PNG

waleeed_mahmood
Creator
Creator
Author

Hi,

Thank you for your time. 

This is what im looking for as a result 

Final Table:
OwnerTotal Value
Tony301500
Steve12000
Miara45000
Bob42500
Sunny80000

 

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

Saravanan_Desingh

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;
waleeed_mahmood
Creator
Creator
Author

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. 

waleeed_mahmood
Creator
Creator
Author

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