Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello there,
I have a Table1 with a list of basic values that belongs to a specific ClassID. And I have a Table 2 with a complete list of Items that belongs to every ClassID.
I need to add to Table1 the missing items that are listed on Table2, considering also the typeID.
The final result most be something like:
TypeID | ClassID | ItemID | ItemName | Amount |
---|---|---|---|---|
1 | 10 | 100 | A | 1000 |
1 | 10 | 200 | B | 1000 |
1 | 10 | 300 | C | 1000 |
1 | 10 | 400 | D | 2000 |
1 | 10 | 500 | E | 3000 |
1 | 20 | 1100 | FF | 100 |
1 | 20 | 1200 | GG | 200 |
1 | 20 | 1300 | HH | 300 |
2 | 10 | 400 | D | 2000 |
2 | 10 | 100 | A | 1000 |
2 | 10 | 200 | B | 1000 |
2 | 10 | 300 | C | 1000 |
2 | 10 | 500 | E | 3000 |
I attach the Qlikview file.
Thanks for any advice.
Best regards,
See attached qvw.
Hello Gysbert,
This is really nice. But I forgot to mention the importance of the field “TypeID”.
In your method the missing fields are added without considerer the “TypeID” that belongs to. If I added a register with TypeID =2 on the Table1:
TypeID | ClassID | ItemID | ItemName | Amount |
---|---|---|---|---|
2 | 10 | 400 | D | 2000 |
It means, that the final table must have in addition the missing fields for TypeID = 1 and TypeID = 2. Something like:
TypeID | ClassID | ItemID | ItemName | Amount |
---|---|---|---|---|
1 | 10 | 100 | A | 1000 |
1 | 10 | 200 | B | 1000 |
1 | 10 | 300 | C | 1000 |
1 | 10 | 400 | D | 2000 |
1 | 10 | 500 | E | 3000 |
1 | 20 | 1100 | FF | 100 |
1 | 20 | 1200 | GG | 200 |
1 | 20 | 1300 | HH | 300 |
2 | 10 | 400 | D | 2000 |
2 | 10 | 100 | A | 1000 |
2 | 10 | 200 | B | 1000 |
2 | 10 | 300 | C | 1000 |
2 | 10 | 500 | E | 3000 |
Thanks in advance for any help.
Table 2 doesn't have a TypeID field. So how is the TypeID determined?
That is the point. Unfortunately I just have TypeID field on Table1 and not on the Table2.
I was thinking that maybe an analysis via GROUP BY TypeID from the Table1 and then look for the missing fields of ItemID could be the way to do it. But it is more complicated than expected.
I will appreciate any light on this matter.