How to merge data from two fields?

[#1]

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

[MayilVahanan]

Hi

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.

Or

You can simply use

LOAD

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

[Team 1],

[Team 2],

Duration

From TableName;

Hope that helps

[bbi_mba_76]

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;

[salto]

Hello,

hope this helps... PFA my version.

Regards.

[tresesco]

One example is attached here too.

[CELAMBARASAN]

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

[#7]

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

Any other ideas?

[CELAMBARASAN]

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

[#9]

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!

[CELAMBARASAN · Accepted solution]

If ID is Unique throughout the all the records.

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