topic Re: Make table with only unique values in App Development

Make table with only unique values

justISO — Wed, 03 Nov 2021 13:50:55 GMT

Hi all, I'm struggling a bit with scenario, where I have two similar tables and I want to merge them into one and show only unique values. Basically, with this:

cust_table_1:
LOAD * INLINE [
custID, cust_name,

111, John
222, Mark
333, Peter
777, Ana
888, Henric
];

cust_table_2:
LOAD * INLINE [
custID, cust_name_2,

333, PeterPeter
444, Sara
555, Yvan
111, JohnJohn
];

TEMP:
NoConcatenate
Load distinct
*
resident cust_table_1
;

concatenate (TEMP)
LOAD *
resident cust_table_2
Where exists (custID)
;

drop table cust_table_1;
drop table cust_table_2;

I get this:

custID	cust_name	cust_name_2
111	John	-
111	-	JohnJohn
222	Mark	-
333	Peter	-
333	-	PeterPeter
444	-	Sara
555	-	Yvan
777	Ana	-
888	Henric	-

but I would like to merge and show only 1 row for each unique custID like this:

custID	cust_name	cust_name_2
111	John	JohnJohn
222	Mark	-
333	Peter	PeterPeter
444	-	Sara
555	-	Yvan
666	Ana	-
777	Henric	-

I tried use max like this:

TEMP_cust:
load
custID,
max(cust_name) as cust_name,
max(cust_name_2) as cust_name2
resident TEMP
group by custID;
drop table TEMP;

but it only show me empty table with unique custIDs.

Is it possible to do this at all?

Re: Make table with only unique values

maxgro — Wed, 03 Nov 2021 17:46:16 GMT

try with a join

cust_table_1:
LOAD * INLINE [
custID, cust_name,

111, John
222, Mark
333, Peter
777, Ana
888, Henric
];

cust_table_2:
LOAD * INLINE [
custID, cust_name_2,

333, PeterPeter
444, Sara
555, Yvan
111, JohnJohn
];

T:
NOCONCATENATE LOAD custID, cust_name Resident cust_table_1;

JOIN (T) LOAD custID, cust_name_2 Resident cust_table_2;

DROP TABLES cust_table_1, cust_table_2;

Re: Make table with only unique values

justISO — Thu, 04 Nov 2021 06:11:42 GMT

Works like a charm, thank you!
I'm outsmarted myself thinking that together with join statement must go these inner/left.. prefixes. Silly me