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

[Par123]

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:

ID	NAME	SYSTEM
1	Harry	B1
1	Tom	A1
2	Jack	A1
3	Sam	B1

I am expecting this result:

ID	NAME	SYSTEM
1	Harry	B1
2	Jack	A1
3	Sam	B1

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

[Par123]

Hi All,

Does anyone know how can I implement this in qlikview.

Kindly provide some suggestions.

Thanks

[tresesco]

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

[Par123]

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

[tresesco · Accepted solution]

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]

Thanks for the help.

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

[Par123]

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]

YES! you are right

[Par123]

Hi Tresesco,

It worked ! Thanks for your help 🙂

Thanks