topic Re: Populate missing data on Table1 from Table2 in QlikView

Populate missing data on Table1 from Table2

Anonymous — Wed, 20 Feb 2013 14:31:52 GMT

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,

Re: Populate missing data on Table1 from Table2

Gysbert_Wassenaar — Wed, 20 Feb 2013 15:23:45 GMT

See attached qvw.

Re: Populate missing data on Table1 from Table2

Anonymous — Wed, 20 Feb 2013 19:31:03 GMT

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.

Re: Populate missing data on Table1 from Table2

Gysbert_Wassenaar — Thu, 21 Feb 2013 08:42:19 GMT

Table 2 doesn't have a TypeID field. So how is the TypeID determined?

Re: Populate missing data on Table1 from Table2

Anonymous — Thu, 21 Feb 2013 08:53:26 GMT

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.