topic Left Join Causing duplicates in QlikView

Left Join Causing duplicates

Wed, 14 Oct 2015 12:32:47 GMT

I am left joining a new table to my main table via an account number and a production date. My new table can contain more than one of the same account number/production date combinations (because of mistakes in database entry). This is causing the data in the first table to be duplicated for each instance of the same account number/ production date in the second table. Looks something like this:

Table 1

ACCT_NBR PROD_DT BALANCE

12345 10/13/15 $100

Table 2

ACCT_NBR PROD_DT NAME

12345 10/13/15 SAM

12345 10/13/15 SAMANTHA

Resulting Table

ACCT_NBR PROD_DT NAME BALANCE

12345 10/13/15 SAM $100

12345 10/13/15 SAMANTHA $100

Is there any way to handle this in QlikView so that if there is an error in the data the balance will not be counted twice?

Thanks!

Re: Left Join Causing duplicates

hic — Wed, 14 Oct 2015 13:09:03 GMT

All joins - including the left join - can cause duplication of records. You should use Applymap instead. See Don't join - use Applymap instead

HIC

Re: Left Join Causing duplicates

jonathandienst — Wed, 14 Oct 2015 13:14:24 GMT

I would usually use ApplyMap, but here is another alternative to catch all the names:

Left Join ([Table 1])

LOAD ACCT_NBR, PROD_DT, Concat(NAME, ', ') As NAMES

FROM [Table 2]

GROUP BY ACCT_NBR, PROD_DT

;

Re: Left Join Causing duplicates

rohit214 — Wed, 14 Oct 2015 13:16:30 GMT

Hi try below code

Table 1

Load ACCT_NBR , PROD_DT ,ACCT_NBR &*PROD_DT as key , BALANCE from xyz.qvd;

left join

Load ACCT_NBR &*PROD_DT as key , Maxstring(NAME) as NAME from abc.qvd grup by ACCT_NBR , PROD_DT,

hope it helps.

Thanks

Rohit

Re: Left Join Causing duplicates

shambhub — Wed, 14 Oct 2015 13:27:30 GMT

Mathew,

You will get duplicate records because there are two different names for same acc no and date. Hence what you can do is make repeating column value as zero. I have written for the same.

Table1:

LOAD [ACCT_NBR ],

PROD_DT,

BALANCE

FROM

(ooxml, embedded labels, table is Sheet2);

Left join(Table1)

Table2:

LOAD [ACCT_NBR ],

PROD_DT,

[NAME ]

FROM

(ooxml, embedded labels, table is Sheet3);

Final_Table:

Load

[ACCT_NBR ] as Acc_No,

PROD_DT as Prod_Date,

[NAME ]as Per_Name,

If(Previous([ACCT_NBR ]) = [ACCT_NBR ] and Previous(PROD_DT) = PROD_DT, BALANCE,0) as Prod_Bal

Resident Table1;

Drop Table Table1;

Acc_No	Per_Name	Prod_Bal	Prod_Date
12345	SAMANTHA	$100.00	10/13/15
12345	SAM	0	10/13/15

Best Regards

Shambhu B