topic Re: Adding Rows Based On Partial Match in QlikView

Adding Rows Based On Partial Match

Anonymous — Fri, 06 Feb 2015 19:36:13 GMT

Need to bring in some additional rows into the Fact table from another source. Some of the rows are common but I need to add only those that are not present in FACT currently. But rows should be entered based on 2 out 3 common fields. For example, I need to match SalesPersonID and ProdName only, even though Category also happens to be a common field.

Appreciate any thoughts.

FACT:

SalesPersonID	ProdName	Category	Region	$Sales
adam	car	cat1	Asia	15000
john	truck	cat3	Americas	40000
john	car	cat1	Europe	22000
john	suv	cat2	Americas	35000
mike	suv	cat2	Americas	33000
mike	car	cat1	Europe	23000

2nd Source:

SalesPersonID	ProdName	Category
adam	car	cat1
adam	suv	cat2
john	suv	cat2
mike	car	cat1
mike	truck	cat3

Desired Resulting Table:

SalesPersonID	ProdName	Category	Region	$Sales
adam	car	cat1	Asia	15000
adam	suv	cat2
john	truck	cat3	Americas	40000
john	car	cat1	Europe	22000
john	suv	cat2	Americas	35000
mike	suv	cat2	Americas	33000
mike	car	cat1	Europe	23000
mike	truck	cat3

Re: Adding Rows Based On Partial Match

Frank_Hartmann — Fri, 06 Feb 2015 20:03:32 GMT

Just join the two tables:

Fact:

Load * Inline [

SalesPersonID, ProdName, Category, Region, $Sales

adam, car, cat1, Asia, 15000

john, truck, cat3, Americas, 40000

john, car, cat1, Europe, 22000

john, suv, cat2, Americas, 35000

mike, suv, cat2, Americas, 33000

mike, car, cat1, Europe, 23000];

join

Source:

Load * Inline [

SalesPersonID, ProdName, Category

adam, car, cat1

adam, suv, cat2

john, suv, cat2

mike, car, cat1

mike, truck, cat3];

Hope it helps!

Re: Adding Rows Based On Partial Match

Anonymous — Fri, 06 Feb 2015 20:07:24 GMT

Understand, but I need joining based on two fields not three. From my original post:

"But rows should be entered based on 2 out 3 common fields. For example, I need to match SalesPersonID and ProdName only, even though Category also happens to be a common field."