Merge of 2 Tables

[peschu123]

Hi,

I have a nasty problem in my script. I think I have to do this with 2 steps but, I'm not sure. This drives me crazy...

2 tables which look like this:

TBL1:

    Key1,Key2,Time

    100,1, 10

    100,2, 15

    100,3, 20

TBL2:

    Key1,Key2,Time

    100,1, 10

    100,2

    100,3

    100,4,15

    200,1,10

    200,2

    200,3,30

Empty fields of a table should be filled (normally only TBL2 has gaps) and no duplicate Values(Key1&Key2) at all.

Final table would look like this:

FINALTBL:

Key1,Key2,Time

    100,1, 10

    100,2, 15

    100,3, 20

    100,4,15

    200,1,10

    200,2

    200,3,30

I hope somebody could help me. I tried this earlier. And thought I could just make a cut a specific Key1 and then concatenate the new keys. But this is not possible.

Thank you in advance.

Peter

[Anonymous · Accepted solution]

Peter, try this:

FINALTBL:
LOAD DISTINCT
Key1,
Key2,
Time,
Key1 & Key2 as Key
FROM TBL1;

CONCATENATE (FINALTBL) LOAD DISTINCT
Key1,
Key2,
Time
FROM TBL2
WHERE not exists(Key, Key1 & Key2);

DROP FIELD Key;

It should work with the data in your example.  I can't tell if it covers all possible situations, the requirement is not quite clear to me.

Regards,
Michael

[peschu123]

Hi again,

Right on my way home the solution came to me. (I think so)

See attached file.

I think 3 Steps are necessary:

1. Generate one composite key from key1 and key2 and rename the fields in one table, except the key field

2. outer join or right join  ... in my case both works.. Anyone more experienced here who can tell me which join i should use?

3. Loading fields in a seperate table using IF statements

I don't know if this is the best solution and I have to try it in a real environment. But I think it will work as supposed. If anyone has a smarter/better solution...you are very welcome!

Regards,

Peter

[Anonymous]

My solutions also come often on the way home 🙂

You got it right - the composite key is The Key.

Outer join will guarantee that you get the rows from both tables, if this is what you want.  The word "outer" is optional, in QV you can use "join", it is the same as "outer join".  Right join removes data from the first table if they not exist in the second.  In your case it is the same because all keys from the first table exist in the second.

Regards,
Michael

[peschu123]

HI Michael,

thank you for your reply.

It works very well, it's a lot better than my "solution". Thank you very much.

The requirement is to fill missing field values in TBL2 with values from TBL1. TBL1 is old data from a database. The fields exist in TBL2 but the older ones are empty(but not all). Now there is an interface and the data is transferred every day into a datawarehouse. But the values from older datasets cannot be transferred into the dwh (for several reasons). So I have to bring them together in qlikview.

I hope this clears things a bit. I will play around with it in the real environment tomorrow.

I created a qwv...see attachment.

Big thanks and have nice day.

[Anonymous]

Hi Peter,

Looks fine, the only additional recommendation for your example is to add this at the end of the script, removing unnecessary fields/tables:

DROP TABLE TBL1;

DROP FIELD Key;

Regards,

Michael

[peschu123]

Thank you for your recommendation.

I integrated your solution in my project. It saves me some IF() etc.opposed to my solution. And it works pretty well. Thank you for that!