2 fields into 1 with unique values that is connected

[Nolgath]

hello guys,

I have a big question, is it possible to put 2 fields that have some values that are the same, into 1 field where you appears unique values AND if you use the new field for a filter for example then it is connected with the other 2 fields? So i can create a filter will all values available in those 2 fields in order to make it easier for the user? Or make a new dimension but with this new field with all unique values inside? 

for example:

seller_kam , buyer_kame

john,mike

elizabeth,sam

michel,igor

New field:

john

mike

elizabeth

sam

michel

igor

what you guys think?

[KGalloway]

I think the following script may solve your problem:

original:
load * inline [
seller_kam , buyer_kame
john,mike
elizabeth,sam
michel,igor
mike,john
]
;

combined_table_1:
load
seller_kam as one_field
resident original;
concatenate
load
buyer_kame as one_field
resident original;

noconcatenate
combined_table_2:
load
distinct one_field
resident combined_table_1;

drop tables original, combined_table_1;

[combined_table_1] actually combines the fields. [combined_table_2] should remove any duplicates

[Nolgath]

I managed to do it :

original:
load * Inline [
seller_kam,buyer_kam
Alexandra Eklund,Alexandra Eklund
Andrei Bodis,Andrei Bodis
Boris Flick,Boris Flick
Brian Madsen,Brian Madsen
Brian Rønnemoss,Brian Rønnemoss
Christoph Wildemann,Christoph Wildemann
Fernando Ascensão,Dominiek De Leus
Fredrik Glenning,Fernando Ascensão
Jan-Heinrich Baden,Fredrik Glenning
Jean Pierre Thorin,Internal Sales Team
John Daalhuisen,Jan-Heinrich Baden
Joseph Caruso,Jean Pierre Thorin
Jurij Virant,Johanna Jolanda
Leroy Stryewski,John Daalhuisen
Madalina Popa,Joseph Caruso
Nicklas Isberg,Madalina Popa
Ondřej Kopečný,Marcel Hein
Patrícia Barbosa,Nicklas Isberg
Ramon Bernhardt,Ondřej Kopečný
Robin Johansson,Patrícia Barbosa
Rossana Verazzo,Ramon Bernhardt
Rui Campos,Richard Pettersen
Steen Kristensen,Robin Johansson
Stefan Franzen,Rossana Verazzo
Stephane Magnier,Rui Campos
Thor Thim,Steen Kristensen
Véronique Van de Vorst,Stefan Franzen
,Stephane Magnier
,Thor Thim
,Véronique Van de Vorst
]
;

combined_table_1:
load
seller_kam as one_field
Resident original;
Concatenate
Load
buyer_kam as one_field
Resident original;

NoConcatenate
combined_table_2:
Load
Distinct one_field
Resident combined_table_1;

drop tables original,combined_table_1;

But when i use "one_field" for filtering it does nothing to seller_kam and buyer_kam why?

[KGalloway]

This code removes the original fields seller_kam and buyer_kam. 

Do you want the ability to select something in one_field and see records where seller_kam or buyer_kam is equal to that value?

[Nolgath]

Yes that would be the final goal, is it possible? 

[KGalloway]

Possibly. This link seems to have solved the same problem: https://community.qlik.com/t5/New-to-Qlik-Sense/Two-Fields-into-one-Field-as-Filter/td-p/1462045 

[vinieme12]

as below

FACT:
load *,recno() as rowID inline [
seller_kam , buyer_kame
john,mike
elizabeth,sam
michel,igor
];

CrossTable('Type','Name',1)
load rowID,seller_kam , buyer_kame
Resident FACT;

[Nolgath]

Hello vinieme12,

Thank you for the reply, unfortunately it is not doing quite as i need it, it makes me a field rowID but only seller_kam reacts to it.

I will try to figure this one out, it is really hard to explain what is wrong

thank you guys!

[vinieme12]

Can you post the script that you've used?

[ogster1974 · Accepted solution]

does this work for you.

original:
load * Inline [
seller_kam,buyer_kam
Alexandra Eklund,Alexandra Eklund
Andrei Bodis,Andrei Bodis
Boris Flick,Boris Flick
Brian Madsen,Brian Madsen
Brian Rønnemoss,Brian Rønnemoss
Christoph Wildemann,Christoph Wildemann
Fernando Ascensão,Dominiek De Leus
Fredrik Glenning,Fernando Ascensão
Jan-Heinrich Baden,Fredrik Glenning
Jean Pierre Thorin,Internal Sales Team
John Daalhuisen,Jan-Heinrich Baden
Joseph Caruso,Jean Pierre Thorin
Jurij Virant,Johanna Jolanda
Leroy Stryewski,John Daalhuisen
Madalina Popa,Joseph Caruso
Nicklas Isberg,Madalina Popa
Ondřej Kopečný,Marcel Hein
Patrícia Barbosa,Nicklas Isberg
Ramon Bernhardt,Ondřej Kopečný
Robin Johansson,Patrícia Barbosa
Rossana Verazzo,Ramon Bernhardt
Rui Campos,Richard Pettersen
Steen Kristensen,Robin Johansson
Stefan Franzen,Rossana Verazzo
Stephane Magnier,Rui Campos
Thor Thim,Steen Kristensen
Véronique Van de Vorst,Stefan Franzen
,Stephane Magnier
,Thor Thim
,Véronique Van de Vorst
]
;

combined_table_1:
load
*,
'seller_kam' as fact_type,
seller_kam as one_field
Resident original;
Concatenate
Load
*,
'buyer_kam' as fact_type,
buyer_kam as one_field
Resident original;

NoConcatenate
Filter:
Load
Distinct one_field
Resident combined_table_1;

drop tables original;