topic Re: How to merge data from two fields? in QlikView

How to merge data from two fields?

Wed, 16 Oct 2013 09:52:13 GMT

Hi all,

I want to merge two fields of a table.I have managed this by using concatenate, but by selecting an item in a listbox with this new created field there is a selection made, but with no effect on any tables or charts.

Here is part of my dataset:

match Team 1 Team 2 Duration

Belgium - Scotland Belgium Scotland 1:30:00

Wales - Serbia Wales Serbia 1:35:00

Scotland - Wales Scotland Wales 1:32:00

Serbia - Belgium Serbia Belgium 1:31:00

I have merged the fields Team 1 and Team 2 to the field "Team". So if I select for example Belgium, I want to have the sum of the duration where Belgium was part of the match. But unfortunately selecting a team has no effect on my chart.

This is part of my script, where I combine Team 1 and Team 2:

Filter1:

LOAD

[Team 1] as Team

Resident Data;

Filter2:

Concatenate (Filter1) LOAD

[Team 2] as Team

Resident Data;

Thanks for your help!!

Kathrin

Re: How to merge data from two fields?

MayilVahanan — Wed, 16 Oct 2013 09:57:55 GMT

Try like this

Filter1:

LOAD

[Team 1] &' - ' & [Team 2] as Team

[Team 1]

Resident Data;

Note:

There is no link between Filter1 and Data table. So only there is no reflect in your case.

You can simply use

LOAD

[Team 1] &' - ' & [Team 2] as Team

[Team 1],

[Team 2],

Duration

From TableName;

Hope that helps

Re: How to merge data from two fields?

bbi_mba_76 — Wed, 16 Oct 2013 10:06:10 GMT

Hi,

like this ?

Data:

LOAD * INLINE [

match, Team 1, Team 2, Duration

Belgium - Scotland, Belgium, Scotland, 01:30:00

Wales - Serbia, Wales, Serbia, 01:35:00

Scotland - Wales, Scotland, Wales, 01:32:00

Serbia - Belgium, Serbia, Belgium, 01:31:00

];

Filter:

LOAD

match,

[Team 1] as Team

Resident Data;

Join (Filter)

LOAD

match,

[Team 2] as Team

Resident Data;

Re: How to merge data from two fields?

salto — Wed, 16 Oct 2013 10:07:21 GMT

Hello,

hope this helps... PFA my version.

Regards.

Re: How to merge data from two fields?

tresB — Wed, 16 Oct 2013 10:15:49 GMT

One example is attached here too.

Re: How to merge data from two fields?

CELAMBARASAN — Wed, 16 Oct 2013 10:34:25 GMT

Hi,

I say you approach is nearly correct. but you missed the link

Filter1:

LOAD

match,

[Team 1] as Team

Resident Data;

Filter2:

Concatenate (Filter1)

LOAD

match,

[Team 2] as Team

Resident Data;

Now it will work as per your expectation

Re: How to merge data from two fields?

Wed, 16 Oct 2013 11:28:50 GMT

I tried this, but with "Team" the result is always duration=0

Any other ideas?

Re: How to merge data from two fields?

CELAMBARASAN — Wed, 16 Oct 2013 11:31:01 GMT

I dont think this approach will fail. Could you please attach the sample?

Re: How to merge data from two fields?

Wed, 16 Oct 2013 12:40:01 GMT

Found the answer myself: to add 'match' was not enough, I had to include the field "ID" in Filter 1 and Filter 2 as a link. "match" was not unique obviously.

Thanks to all for your input!

Re: How to merge data from two fields?

CELAMBARASAN — Wed, 16 Oct 2013 13:02:07 GMT

If ID is Unique throughout the all the records.

Then make ID as link instead of combining match and ID.