Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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 (4)
1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

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
Highlighted
Specialist III
Specialist III

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

Highlighted
Creator
Creator

Hi,

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

Thanks

Highlighted
Specialist III
Specialist III

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, 
];
Highlighted
Specialist III
Specialist III

Output

commQV38.PNG

Highlighted
Creator
Creator

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

Highlighted
Specialist III
Specialist III

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

Highlighted
Specialist III
Specialist III

commQV40.PNG

Highlighted
Creator
Creator

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. 

Highlighted
Creator
Creator

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