Find all possible occurrences of a field in a column on another column of the same table

[Pako]

Hi

It's my first approach using Qlik Sense and I need to find all possible occurrences of a field in a column on another column of the same table and extract the data from other columns within the same table. This is the example:

Employee_ Number	Name	Net_ID	Employer
202992	Jose	sd0001	239999
338373	Emily	sd0002	202992
546454	Frank	sd0003	202992
92907	Carol	sd0004	202992
9282554	Gabriela	sd0005	202992
I need a table like this
Employer	Employee_ Number	Name	Net_ID
202992	338373	Emily	sd0002
202992	546454	Frank	sd0003
202992	92907	Carol	sd0004
202992	9282554	Gabriela	sd0005

[justISO · Accepted solution]

Hi, you could try something like this:

example_data:
LOAD * INLINE [
Employee_Number, Name, Net_ID, Employer
202992, Jose, sd0001, 239999
338373, Emily, sd0002, 202992
546454, Frank, sd0003, 202992
92907, Carol, sd0004, 202992
9282554, Gabriela, sd0005, 202992];

NoConcatenate
temp:
LOAD DISTINCT
Employee_Number as Employer
RESIDENT example_data;

INNER JOIN
LOAD
Employer,
Employee_Number,
Name,
Net_ID
RESIDENT example_data;

DROP TABLE example_data;

[Pako]

Thank you very much for the answer, it has been very useful.

Just one more question. How could I add the Net_ID of the employer into the table.

Employer	Net_ID_Employer	Employee_ Number	Name	Net_ID
202992	sd0001	338373	Emily	sd0002
202992	sd0001	546454	Frank	sd0003
202992	sd0001	92907	Carol	sd0004
202992	sd0001	9282554	Gabriela	sd0005

[MarcoWedel]

Isn't this just the same table missing the first row and having a different order of columns?

[Pako]

Hi, yes it's the same table, only I also need to include the Net_ID_Employer field.

Maybe it's something trivial, but I'm not a programmer.

[MarcoWedel]

maybe something like this?

table1:
Hierarchy (Employee_Number, Employer, Net_ID, 'Net_ID_Employer')
LOAD * Inline [
Employee_Number,	Name,	Net_ID,	Employer
202992,		Jose,		sd0001,	239999
338373,		Emily,		sd0002,	202992
546454,		Frank,		sd0003,	202992
92907,		Carol,		sd0004,	202992
9282554,	Gabriela,	sd0005,	202992
];

[justISO · Accepted solution]

For Net_ID_Employer you can add 1 row in my previous suggested script like:

...

temp:
LOAD DISTINCT
Employee_Number as Employer,
Net_ID as Net_ID_Employer
RESIDENT example_data;

...

[Pako]

I knew it could be trivial, thank you very much. It is exactly what I needed.