Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Merging rows of fields based on uniqueness of another

Hi community,

I have two fields from the same table A, and B.  I want to make them 1 to 1 so that one row of A has only 1 row of B.

If there are more than 1 row of B, I would like them all to be put into one row. 

We could call this field C and it would then match 1 to 1 to A.

Any ideas?

1 Solution

Accepted Solutions
msteedle
Luminary Alumni
Luminary Alumni

I think you are looking for the Concat() function coupled with a GROUP BY in the load. For example, this...

Base:

LOAD A,

          Concat(B, ' ') as C

GROUP BY A

;

LOAD * INLINE [

    A, B

    Michael, Smith

    Tom, G.

    Tom, Jones

    Joe, David

    Joe, Smith

];

...yields this:

A C
Michael Smith
Tom G. Jones
Joe David Smith

View solution in original post

7 Replies
chematos
Specialist II
Specialist II

Hi Brandon.

I think that using group by A. Could you do any operation with B?? SUM, AVG??something??I don´t know which is the relation between A and B.

Load

A,

sum(B)

from table

group by A;

Hope this helps

Not applicable
Author

A and B are fields with characters.  Like two names.  A is first name, B is other names like middle and last.  I want C to have all other names of the A. So for any first name, rather than having a middle and last name associated in B, I want it to have them joined into one row in C.

chematos
Specialist II
Specialist II

May be this, let me know if it helps.

Temp:

Load distinct

A,

A & '_' & B as C

from wherever;

left Join

Load

A,

C

Resident Temp;

msteedle
Luminary Alumni
Luminary Alumni

I think you are looking for the Concat() function coupled with a GROUP BY in the load. For example, this...

Base:

LOAD A,

          Concat(B, ' ') as C

GROUP BY A

;

LOAD * INLINE [

    A, B

    Michael, Smith

    Tom, G.

    Tom, Jones

    Joe, David

    Joe, Smith

];

...yields this:

A C
Michael Smith
Tom G. Jones
Joe David Smith
Not applicable
Author

A: What if I have other fields in the table like D and F?  I don't to do anything to them just load them too.                  B: You are almost right.  That joined the middle and last names but now it pins each first and last name to each first name.  So I'm getting:  Michael Smith, Michael G. Jones, Michael David Smith, etc...

chematos
Specialist II
Specialist II

I think this should work using

TABLE1:

LOAD

A,

B,

D,

E

from Wherever;

Temp:

Load distinct

A,

A & ' ' & B as C

Resident TABLE1;

left Join(TABLE1)

Load

A,

C

Resident Temp;

May be I´m wrong, let me know please.

Not applicable
Author

That was a very helpful correct answer, now for that field that we concateneated, is there anyway to load the distinct names of it?  Right now, if the field shows up more than once, I get a record of the same thing repeated like EFS, EFS, EFS.  Is there anyway to get this to be just EFS?