Concatenate / Updating the table

[RoyBatty]

Hi,

There are two tables:

[results]:
Load * Inline
[
USER_ID, VALUE
1, 20
2, 15
1, 25
3, 10
3, 20
1, 15
];

[result_updates]:
Load * Inline
[
USER_ID, VALUE
1, 20
2, 15,
1, 25
];

They will be concatenated and the new table will look like this::

USER_ID, VALUE
1, 20
2, 15
1, 25
3, 10
3, 20
1, 15

1, 20
2, 15,
1, 25

However, we want to remove records from the first table that have a user_id for which there are records in the second table. So this is the result we want (order doesn't matter):

USER_ID, VALUE
3, 10
3, 20

1, 20
2, 15,
1, 25

So, since in the second table (result_updates) there are records with user_id = 1 and 2, then the records from the first table with user_id = 1 and 2 will be removed.

---------------------------------------------------------------------------------------

We tried the following:

[result_updates]:
Load * Inline
[
USER_ID, VALUE
1, 20
2, 15,
1, 25
];


Concatenate (result_updates)
Load * Inline
[
USER_ID, VALUE
1, 20
2, 15
1, 25
3, 10
3, 20
1, 15
]
WHERE NOT EXISTS(USER_ID, USER_ID);

but the result is:

One record is missing - there is only one record with user id = 3. This is the result we want:

USER_ID, VALUE
1, 20
2, 15,
1, 25

3, 10
3, 20

Do you have any suggestions on how we can achieve this? 🤔

[marksouzacosta · Accepted solution]

Hi @RoyBatty ,

Try this code:

[result_updates]:
Load 
USER_ID,
VALUE,
USER_ID AS TEMP_USER_ID
Inline [
USER_ID, VALUE
1, 20
2, 15,
1, 25
];


Concatenate (result_updates)
Load * Inline [
USER_ID, VALUE
1, 20
2, 15
1, 25
3, 10
3, 20
1, 15
]
WHERE NOT EXISTS(TEMP_USER_ID, USER_ID)
;

DROP FIELD TEMP_USER_ID;

[MatheusC]

@RoyBatty 
As a complement to what has already been helped, see the example below:

// Changing the name of the USER_ID field
[result_updates]:
Load
USER_ID as USER_result,
VALUE
Inline
[
USER_ID, VALUE
1, 20
2, 15,
1.25
];

//================================================ =====================//

// Concatenating records where there is no USER_result in USER_ID
Concatenate([result_updates])
[results]:
Load
*
 where not(Exists('USER_result',USER_ID));
load * Inline [
USER_ID, VALUE
1, 20
2, 15
1.25
3, 10
3, 20
1, 15
];

//================================================ =====================//

// Loading data from result_updates table
Final Table:
Load
if(IsNull(USER_result),USER_ID,USER_result) as ID, // If USER_result field is null, return USER_ID otherwise USER_result
*
Resident [result_updates];

//================================================ =====================//

// Deleting result_updates table
Drop Tables [result_updates];

// excluding fields USER_result,USER_ID
drop fields USER_result,USER_ID from TableFinal;

Regarts, Matheus

[RoyBatty]

Thank you!