Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
RoyBatty
Contributor III
Contributor III

Concatenate / Updating the table

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:

RoyBatty_0-1715875432211.png

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? 🤔

 

Labels (1)
1 Solution

Accepted Solutions
marksouzacosta
Partner - Specialist
Partner - Specialist

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;

 

Read more at Data Voyagers - datavoyagers.net

View solution in original post

3 Replies
marksouzacosta
Partner - Specialist
Partner - Specialist

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;

 

Read more at Data Voyagers - datavoyagers.net
MatheusC
Specialist
Specialist

@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;

 

MatheusC_0-1715884054315.png

MatheusC_1-1715884076868.png



Regarts, Matheus

 

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!
RoyBatty
Contributor III
Contributor III
Author

Thank you!