Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Left Join Causing duplicates

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!

4 Replies
Employee
Employee

Re: Left Join Causing duplicates

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

HIC

MVP
MVP

Re: Left Join Causing duplicates

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

;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rohit214
Contributor III

Re: Left Join Causing duplicates

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
Contributor

Re: Left Join Causing duplicates

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_NameProd_BalProd_Date
12345 SAMANTHA $100.0010/13/15
12345 SAM 010/13/15

Best Regards

Shambhu B

Community Browser