Lookup value based on multiple conditions between two tables

[xupmaupl]

Hello everyone,

I am having two tables below. 

Table: A

Branch	Buy Ccy	Buy Amount	Sell Ccy	Sell Amount
A	USD	200	EUR	190
B	EUR	180	USD	190
C	JPY	200	EUR	50

Table B:

Customer	Buy Ccy	Buy Amount	Sell Ccy	Sell Amount
AA	EUR	190	USD	200
BB	EUR	180	USD	190
CC	JPY	200	EUR	50
DD	EUR	50	JPY	200

I need to left join Table B to Table A based on multiple conditions.

Condition 1: Left join if (Table A Buy Ccy = Table B Buy Ccy and Table A Buy Amount = Table B Buy Amount and Table A Sell Ccy = Table B Sell Ccy  and Table A Sell Amount = Table B Sell Amount) or (Table A Buy Ccy = Table B Sell Ccy and Table A Buy Amount = Table B Sell Amount and Table A Sell Ccy = Table B Buy Ccy  and Table A Sell Amount = Table B Buy Amount)

As Brach sell and buy with the Customer but the Table B data is random. So need to compare both the combination of Ccy and Amount between two tables regardless sell or buy.

Condition 2: A check is needed here. If there are more than one Customer in Table B match with a Branch in the Table A, leave Customer blank in the joined table. For example, both Customer CC and DD match Branch C based on the Condition 1, leave the customer blank.

The final table should be like:

Branch	Customer	Buy Ccy	Buy Amount	Sell Ccy	Sell Amount
A	AA	USD	200	EUR	190
B	BB	EUR	180	USD	190
C		JPY	200	EUR	50

Could you please provide the possible solution for this scenario?

Thanks in advance. 

Kind Regards,

Riva 

[xupmaupl]

I plan to use two loops to achieve it.

The first loop will take the value row by row from the first table and the second loop will do the same for the second table. 

I set the FinalCustomer variable as '' initially and if the j iteration achieve the IF condition, make FinalCustomer as the Customer value from the current j row.

Another valuable vCount was also set to check if more than one match between two tables. 

After each i loop, left join the FinalCustomer value as Customer to the Branch table if vCount = 1. If  vCount<>1, leave Customer blank.

Branch:
LOAD [Branch],
[Buy Ccy],
[Buy Amount],
[Sell Ccy],
[Sell Amount]
FROM
[I:\Desktop\Qlikview\File1.xlsx]
(ooxml, embedded labels);

Customer:
LOAD [Customer] as CustomerTemp1,
[Buy Ccy],
[Buy Amount],
[Sell Ccy],
[Sell Amount]
FROM
[I:\Desktop\Qlikview\File2.xlsx]
(ooxml, embedded labels);

FOR i = 0 to FieldValueCount('Branch')-1;
LET vCount = 0;
LET Buy_Ccy1 = Peek('Buy Ccy',i,'Branch');
LET Buy_Amount1 = Alt(fabs(Peek('Buy Amount',i,'Branch')),0);
LET Sell_Ccy1 = Peek('Sell Ccy',i,'Branch');
LET Sell_Amount1 = Alt(fabs(Peek('Sell Amount',i,'Branch')),0);
LET FinalCustomer = '';

FOR j = 0 to FieldValueCount('CustomerTemp1')-1;
LET Buy_Ccy2 = Peek('Buy Ccy',j,'Customer');
LET Buy_Amount2 = Alt(fabs(Peek('Buy Amount',j,'Customer')),0);
LET Sell_Ccy2 = Peek('Sell Ccy',j,'Customer');
LET Sell_Amount2 = Alt(fabs(Peek('Sell Amount',j,'Customer')),0);
LET CustomerTemp2 = Peek('CustomerTemp1',j,'Customer');
IF (Buy_Ccy1 = Buy_Ccy2 and Buy_Amount1 = Buy_Amount2 and Sell_Ccy1 = Sell_Ccy2 and Sell_Amount1 = Sell_Amount2) or (Buy_Ccy1 = Sell_Ccy2 and Buy_Amount1 = Sell_Amount2 and Sell_Ccy1 = Buy_Ccy2 and Sell_Amount1 = Buy_Amount2) THEN
FinalCustomer = CustomerTemp2;
vCount = $(vCount) +1;
ENDIF
NEXT j
If $(vCount) = 1 THEN
LOAD '$(FinalCustomer)' as Customer
Resident Branch;
ENDIF
NEXT i

However, the final result is like below: 

Branch	Buy Amount	Buy Ccy	Customer	Sell Amount	Sell Ccy
A	200	USD	-	-190	EUR
B	180	EUR	BB	-190	USD
C	200	JPY	CC	-50	EUR
-	50	EUR	DD	-200	JPY
-	190	EUR	AA	-200	USD

How can I correct it?

[xupmaupl]

Eventually I made it work by checking a lot of information in the Qlikview Community. Any advise to optimize it is appreciated.

Branch:
LOAD RecNo() as Dummy,
[Branch],
[Buy Ccy],
[Buy Amount],
[Sell Ccy],
[Sell Amount],
if([Buy Ccy] = 'USD',[Buy Ccy]&fabs([Buy Amount])&[Sell Ccy]&fabs([Sell Amount]),[Sell Ccy]&fabs([Sell Amount])&[Buy Ccy]&fabs([Buy Amount])) as CcyAmtPair
FROM
[I:\Desktop\CVA\Qlikview\File1.xlsx]
(ooxml, embedded labels);

Customer:
LOAD [Customer] as TempCustomer,
[Buy Ccy] as [Customer Buy Ccy],
[Buy Amount] as [Customer Buy Amount],
[Sell Ccy] as [Customer Sell Ccy],
[Sell Amount] as [Customer Sell Amount],
if([Buy Ccy] = 'USD',[Buy Ccy]&fabs([Buy Amount])&[Sell Ccy]&fabs([Sell Amount]),[Sell Ccy]&fabs([Sell Amount])&[Buy Ccy]&fabs([Buy Amount])) as CcyAmtPair
FROM
[I:\Desktop\CVA\Qlikview\File2.xlsx]
(ooxml, embedded labels);

TempFinal:
LOAD 0 as Customer AutoGenerate 0;

FOR i = 0 to FieldValueCount('Branch')-1;
LET vCount = 0;
LET k = $(i)+1;
LET Buy_Ccy1 = Peek('Buy Ccy',i,'Branch');
LET Buy_Amount1 = Alt(fabs(Peek('Buy Amount',i,'Branch')),0);
LET Sell_Ccy1 = Peek('Sell Ccy',i,'Branch');
LET Sell_Amount1 = Alt(fabs(Peek('Sell Amount',i,'Branch')),0);
LET FinalCustomer = '';
LET BlankCustomer = '';

FOR j = 0 to FieldValueCount('TempCustomer')-1;
LET Buy_Ccy2 = Peek('Customer Buy Ccy',j,'Customer');
LET Buy_Amount2 = Alt(fabs(Peek('Customer Buy Amount',j,'Customer')),0);
LET Sell_Ccy2 = Peek('Customer Sell Ccy',j,'Customer');
LET Sell_Amount2 = Alt(fabs(Peek('Customer Sell Amount',j,'Customer')),0);
LET StoreCustomer = Peek('TempCustomer',j,'Customer');
IF (Buy_Ccy1 = Buy_Ccy2 and Buy_Amount1 = Buy_Amount2 and Sell_Ccy1 = Sell_Ccy2 and Sell_Amount1 = Sell_Amount2) or (Buy_Ccy1 = Sell_Ccy2 and Buy_Amount1 = Sell_Amount2 and Sell_Ccy1 = Buy_Ccy2 and Sell_Amount1 = Buy_Amount2) THEN
FinalCustomer = StoreCustomer;
vCount = $(vCount) +1;
ENDIF
NEXT j
If $(vCount) = 1 THEN
TempFinal:
LOAD '$(FinalCustomer)' as Customer,
Num#('$(k)') as Dummy
AutoGenerate 1;
ENDIF
NEXT i

DROP Table TempFinal;

[Brett_Bleess]

Riva, it would be most helpful if you attach the app, or a sample app that has the actual data model etc.  You can scramble confidential fields of data in the app by going to Settings\Document Properties\Scrambling tab and selecting which fields to scramble there...  This should allow folks to have a look to see if they can offer any other suggestions for optimizing things. 

Regards,
Brett