Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
All joins - including the left join - can cause duplication of records. You should use Applymap instead. See Don't join - use Applymap instead
HIC
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
;
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
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