Peter, try this:
Key1 & Key2 as Key
CONCATENATE (FINALTBL) LOAD DISTINCT
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.
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.
test.qvw 148.8 K
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!
test.qvw 146.0 K
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.