Left Join Causing duplicates

[#1]

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!

[hic]

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

HIC

[jonathandienst]

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

;

[rohit214]

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

[shambhub]

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