topic Re: How to combine two column in this scenario in QlikView

How to combine two column in this scenario

Naps_230 — Wed, 25 Apr 2018 10:10:23 GMT

Hi Team,

I have some sample data.

Team 1	Team 2	Winner
Srilanka	India	India
Pakistan	India	Pakistan
India	Srilanka	Srilanka
India	England	India
India	Pakistan	India
China	India	India

Result-

Srilanka - 2 Match

Pakistan- 2 Match

China- 1 Match

England- 1 Match

Requirement :

I want to show in bar chart.

How many matches has India played against different teams?

please any one give some suggestion

Re: How to combine two column in this scenario

sunny_talwar — Wed, 25 Apr 2018 12:03:43 GMT

Something like this?

Script

Table:

LOAD * INLINE [

Team 1, Team 2, Winner

Srilanka, India, India

Pakistan, India, Pakistan

India, Srilanka, Srilanka

India, England, India

India, Pakistan, India

China, India, India

];

Dim:

LOAD * Inline [

Dim

];

Straight table with

Dimension

=Pick(Dim, If([Team 1] = 'India', [Team 2]), If([Team 2] = 'India', [Team 1]))

Expression

Count([Team 1])

Re: How to combine two column in this scenario

olivierrobin — Wed, 25 Apr 2018 12:18:18 GMT

you could try a graph with

=if(wildmatch(Team1,'*India*'),Team2,
if(wildmatch(Team2,'*India*'),Team1,'')
)

as dimension

and

sum(1)

as measure

Re: How to combine two column in this scenario

vishalarote — Wed, 25 Apr 2018 12:24:02 GMT

I am trying this in Backend it works hope so it will help you

e.g.

Match:

load *,

India&' '&Other as Play;

load *,

if(WildMatch([Team 1],'India') or WildMatch([Team 2],'India'),'India') as India,

if(SubField(Match,' ',1)<>'India',subfield(Match,' ',1),

if(SubField(Match,' ',2)<>'India',subfield(Match,' ',2))) as Other;

load *,

[Team 1]&' '&[Team 2] as Match inline [

Team 1, Team 2, Winner

Srilanka, India, India

Pakistan, India, Pakistan

India, Srilanka, Srilanka

India, England, India

India, Pakistan, India

China, India, India

];

Re: How to combine two column in this scenario

bc-thebruuu — Wed, 25 Apr 2018 12:35:42 GMT

Result:

Noconcatenate Load Team1 as Team resident Table

where WildMatch(Team2,'India')

Concatenate (Result)

Load Team2 as Team resident Table

where WildMatch(Team2,'India')

And then you just have to count occurences