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: 
Par123
Contributor III
Contributor III

Select one row out of duplicate rows based on a field name

Hi All,

I need some help with the below issue:

 I have two tables which I need to concat.

TEST_A:
LOAD * INLINE [
ID, NAME, SYSTEM
1, Tom, A1
2, Jack, A1
];

Concatenate(TEST_A)
LOAD * INLINE [
ID, NAME, SYSTEM
1, Harry, B1
3, Sam, B1
];

On concatenating I am getting duplicate values:

IDNAMESYSTEM
1HarryB1
1TomA1
2JackA1
3SamB1

 

I am expecting this result:

IDNAMESYSTEM
1HarryB1
2JackA1
3SamB1

 

It means I want to take all records from both the tables but when there are duplicate rows then I want to take the ID which has SYSTEM name as 'B1'.

I am trying to do this at the Qlikview scripting side.

Kindly help.

Thanks

1 Solution

Accepted Solutions
tresesco
MVP
MVP

In that case, you can load the second table first and use exists() to check if an ID is already loaded or not, if loaded (from second table -B), don't load it from table A. Like:

TEST_B:
LOAD * INLINE [
ID, NAME, SYSTEM
1, Harry, B1
3, Sam, B1
];

Concatenate(TEST_B)
TEST_A:
LOAD * INLINE [
ID, NAME, SYSTEM
1, Tom, A1
2, Jack, A1
] where not Exists(ID);

View solution in original post

8 Replies
Par123
Contributor III
Contributor III
Author

Hi All,

Does anyone know how can I implement this in qlikview.

Kindly provide some suggestions.

Thanks

tresesco
MVP
MVP

Can we say that 'B1' would always come from second table?

Par123
Contributor III
Contributor III
Author

yes..B1 is the system name of the second table so it will always come from second table.

tresesco
MVP
MVP

In that case, you can load the second table first and use exists() to check if an ID is already loaded or not, if loaded (from second table -B), don't load it from table A. Like:

TEST_B:
LOAD * INLINE [
ID, NAME, SYSTEM
1, Harry, B1
3, Sam, B1
];

Concatenate(TEST_B)
TEST_A:
LOAD * INLINE [
ID, NAME, SYSTEM
1, Tom, A1
2, Jack, A1
] where not Exists(ID);

Par123
Contributor III
Contributor III
Author

Thanks for the help.

I am trying this in my qlikview file and let you know.

Par123
Contributor III
Contributor III
Author

Hi Tresesco,

one question please.

If 'SYSTEM' column is not present in the table but we know that we need to take the value from the second table TEST_B, then also we can place the second table first? Is that right? like this:

TEST_B:
LOAD * INLINE [
ID, NAME
1, Harry
3, Sam
];

Concatenate(TEST_B)
TEST_A:
LOAD * INLINE [
ID, NAME
1, Tom
2, Jack
] where not Exists(ID);

To get this result:

ID, NAME
1,  Harry
2, Jack

3, Sam

tresesco
MVP
MVP

YES! you are right

Par123
Contributor III
Contributor III
Author

Hi Tresesco,

It worked ! Thanks for your help 🙂

Thanks