Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 |
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
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.
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;
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 |
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...
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.
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?