Skip to main content
Announcements
Global Transformation Awards submissions are open! SUBMIT YOUR STORY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to join with filtering based on both tables

Hello,

I want to load data in QV from two tables. I must join them on one column (X) and restrict them on values from both tables (one from each).

SQL syntax would be

Select A.W,

B.Z

From A, B

where A.X = B.X

and A.W > B.Z

How could I do this in QV scripting?

Thanks, Paul.

1 Solution

Accepted Solutions
Not applicable
Author


[TEMP]:
LOAD
Key,
ValueA
......;
LEFT JOIN ([TEMP])
LOAD
Key,
ValueB
......;
[TABLE]:
NOCONCATENATE LOAD
*
RESIDENT [TEMP]
WHERE ValueA>ValueB;
DROP TABLE [TEMP];


View solution in original post

5 Replies
Not applicable
Author


[TEMP]:
LOAD
Key,
ValueA
......;
LEFT JOIN ([TEMP])
LOAD
Key,
ValueB
......;
[TABLE]:
NOCONCATENATE LOAD
*
RESIDENT [TEMP]
WHERE ValueA>ValueB;
DROP TABLE [TEMP];


cvillani
Contributor II

Hi all, sorry for jumping in after so many years....

I read this post (and the solution) because I have a similar problem.

As far as I understand, correct if I'm wrong, with this solution QV first creates a temp table with all combinations (unfiltered) from both tables, and in a second step it applies the filter by creating and feeding the final table with the filtered rows.

This solution is failing for me because both tables have a high number of rows, so the combinations between the two tables is exploding a huge amount of rows (that probably is exceeding the QV limits).

Is there a different way to overcome this problem?
Thanks for your support

Corrado

BrunPierre
Partner - Master

@cvillani  The resultant table may add rows when there are multiple matches, leading to an increase in row count. To avoid this, it is essential to perform the join on a unique set of keys.

cvillani
Contributor II

Thanks for answering BrunPierre.
 
Let me add some details of my problem.
 
Table "_Controls" contains 850.488 rows with these columns (plus others not relevant for this discussion):
Id
Type
Account
AttributeTypeA (could be % or a specific AttributeA code)
AttributeTypeB (could be % or a specific AttributeB code)
AttributeTypeC (could be % or a specific AttributeC code)
AttributeTypeD (could be % or a specific AttributeD code)
AttributeTypeE (could be % or a specific AttributeE code)
 
Table "_Data" containing 363.527 rows with the following columns (plus others not relevant for this discussion):
Company
Account
AttributeA (contains a specific AttributeA code)
AttributeB (contains a specific AttributeB code)
AttributeC (contains a specific AttributeC code)
AttributeD (contains a specific AttributeD code)
AttributeE (contains a specific AttributeE code)
Currency
Amount
 
In a resulting "_Tmp" table I would need to have the following columns:
Data.Company
Control.Id
Control.Type
Data.Account
Data.AttributeA
Data.AttributeB
Data.AttributeC
Data.AttributeD
Data.AttributeE
Data.Currency
Data.Amount
 
Where Data.Account=Control.Account
And (Control.AttributeA='%' Or Control.AttributeA=Data.AttributeA)
And (Control.AttributeB='%' Or Control.AttributeB=Data.AttributeB)
And (Control.AttributeC='%' Or Control.AttributeC=Data.AttributeC)
And (Control.AttributeD='%' Or Control.AttributeD=Data.AttributeD)
And (Control.AttributeE='%' Or Control.AttributeE=Data.AttributeE)
 
That's why I need to apply the join filters before the cross join is performed.
 
To limit the number of combinations I tried the following:
 
_Tmp:
Load
Id                     As TMP_ControlId
,Type                   As TMP_ControlType
,Account                As TMP_Account
,AttributeTypeA         As TMP_AttributeTypeA
,AttributeTypeB         As TMP_AttributeTypeB
,AttributeTypeC         As TMP_AttributeTypeC
,AttributeTypeD         As TMP_AttributeTypeD
,AttributeTypeE         As TMP_AttributeTypeE
 
,Account&AttributeTypeA As TMP_AccountAttrA
,Account&AttributeTypeB As TMP_AccountAttrB
,Account&AttributeTypeC As TMP_AccountAttrC
,Account&AttributeTypeD As TMP_AccountAttrD
,Account&AttributeTypeE As TMP_AccountAttrE
Resident _Controls;
Inner Join
Load
Company                As TMP_Company
,Account                As TMP_Account
,AttributeA             As TMP_AttributeA 
,AttributeB             As TMP_AttributeB 
,AttributeC             As TMP_AttributeC 
,AttributeD             As TMP_AttributeD 
,AttributeE             As TMP_AttributeE 
,Currency               As TMP_Currency
,Amount                 As TMP_Amount
Resident _Data
Where (
Exists(TMP_AccountAttrA,Account&'%') 
Or 
Exists(TMP_AccountAttrA, Account&AttributeA)
) And (
Exists(TMP_AccountAttrB,Account&'%') 
Or 
Exists(TMP_AccountAttrB, Account&AttributeB)
) And (
Exists(TMP_AccountAttrC,Account&'%') 
Or 
Exists(TMP_AccountAttrC, Account&AttributeC)
) And (
Exists(TMP_AccountAttrD,Account&'%') 
Or 
Exists(TMP_AccountAttrD, Account&AttributeD)
) And (
Exists(TMP_AccountAttrE,Account&'%') 
Or 
Exists(TMP_AccountAttrE, Account&AttributeE)
);
 
In this way, I'm excluding the combinations Account/AttributeX that don't exists, but I'm not fully applying the filter I need.
 
Do you have any idea how could I manage it?
 
Thanks and sorry if I've been so ... verbose.
 
Corrado
cvillani
Contributor II

Hi all, short update.
 
I had to bypass the problem by splitting the join between the two tables in 32 (2^5), considering, for each join, different conditions:
  A B C D E
  ---------
  0 0 0 0 0
  0 0 0 0 1
  0 0 0 1 0
  0 0 0 1 1
  .........
  1 1 1 1 1
where 0 means that the related attribute should be =%, 1 means that the related attribute should be <>%.
 
When the attribute in the _Control table is =% then the related attribute values are taken only from _Data table.
When the attribute in the _Control table is <>% then the attribute value is taken from the _Controls table, and joined with the corresponding attribute form _Data.
 
It is not so elegant, but is should work.
 
// ***************************************************************************
// * First combination: AttrA=% AttrB=% AttrC=% AttrD=% AttrE=%
// ***************************************************************************
_Tmp1:
Load
Id           As TMP1_ControlId
,Type         As TMP1_ControlType
,Account      As TMP1_Account
Resident _Controls
Where AttributeTypeA='%'
And   AttributeTypeB='%'
And   AttributeTypeC='%'
And   AttributeTypeD='%'
And   AttributeTypeE='%';
Inner Join
Load
Company      As TMP1_Company
,Account      As TMP1_Account
,AttributeA   As TMP1_AttributeA 
,AttributeB   As TMP1_AttributeB 
,AttributeC   As TMP1_AttributeC 
,AttributeD   As TMP1_AttributeD 
,AttributeE   As TMP1_AttributeE 
,Currency     As TMP1_Currency
,Amount       As TMP1_Amount
Resident _Data;

// ************************************************************
// * For the first combination, table _Tmp is created
// ************************************************************
_Tmp:
Load
TMP1_ControlId   As TMP_ControlId
,TMP1_ControlType As TMP_ControlType
,TMP1_Account     As TMP_Account
,TMP1_AttributeA  As TMP_AttributeA
,TMP1_AttributeB  As TMP_AttributeB
,TMP1_AttributeC  As TMP_AttributeC
,TMP1_AttributeD  As TMP_AttributeD
,TMP1_AttributeE  As TMP_AttributeE
,TMP1_Currency    As TMP_Currency
,TMP1_Amount      As TMP_Amount
Resident _Tmp1;

Drop Table _Tmp1;

// ***************************************************************************
// * Second combination: AttrA=% AttrB=% AttrC=% AttrD=% AttrE<>%
// ***************************************************************************
_Tmp1:
Load
Id             As TMP1_ControlId
,Type           As TMP1_ControlType
,Account        As TMP1_Account
,AttributeTypeE As TMP1_AttributeE
Resident _Controls
Where AttributeTypeA='%'
And   AttributeTypeB='%'
And   AttributeTypeC='%'
And   AttributeTypeD='%'
And   AttributeTypeE<>'%';
Inner Join
Load
Company        As TMP1_Company
,Account        As TMP1_Account
,AttributeA     As TMP1_AttributeA 
,AttributeB     As TMP1_AttributeB 
,AttributeC     As TMP1_AttributeC 
,AttributeD     As TMP1_AttributeD 
,AttributeE     As TMP1_AttributeE 
,Currency       As TMP1_Currency
,Amount         As TMP1_Amount
Resident _Data;

// ************************************************************
// * For the following combinations, table _Tmp is concatenated
// ************************************************************
Concatenate(_Tmp)
Load
TMP1_ControlId   As TMP_ControlId
,TMP1_ControlType As TMP_ControlType
,TMP1_Account     As TMP_Account
,TMP1_AttributeA  As TMP_AttributeA
,TMP1_AttributeB  As TMP_AttributeB
,TMP1_AttributeC  As TMP_AttributeC
,TMP1_AttributeD  As TMP_AttributeD
,TMP1_AttributeE  As TMP_AttributeE
,TMP1_Currency    As TMP_Currency
,TMP1_Amount      As TMP_Amount
Resident _Tmp1;

Drop Table _Tmp1;
..........................

// ***************************************************************************
// * Last combination: AttrA<>% AttrB<>% AttrC<>% AttrD<>% AttrE<>%
// ***************************************************************************
_Tmp1:
Load
Id             As TMP1_ControlId
,Type           As TMP1_ControlType
,Account        As TMP1_Account
,AttributeTypeA As TMP1_AttributeA
,AttributeTypeB As TMP1_AttributeB
,AttributeTypeC As TMP1_AttributeC
,AttributeTypeD As TMP1_AttributeD
,AttributeTypeE As TMP1_AttributeE

Resident _Controls
Where AttributeTypeA<>'%'
And   AttributeTypeB<>'%'
And   AttributeTypeC<>'%'
And   AttributeTypeD<>'%'
And   AttributeTypeE<>'%';
Inner Join
Load
Company        As TMP1_Company
,Account        As TMP1_Account
,AttributeA     As TMP1_AttributeA 
,AttributeB     As TMP1_AttributeB 
,AttributeC     As TMP1_AttributeC 
,AttributeD     As TMP1_AttributeD 
,AttributeE     As TMP1_AttributeE 
,Currency       As TMP1_Currency
,Amount         As TMP1_Amount
Resident _Data;

// ************************************************************
// * Table _Tmp is concatenated
// ************************************************************
Concatenate(_Tmp)
Load
TMP1_ControlId   As TMP_ControlId
,TMP1_ControlType As TMP_ControlType
,TMP1_Account     As TMP_Account
,TMP1_AttributeA  As TMP_AttributeA
,TMP1_AttributeB  As TMP_AttributeB
,TMP1_AttributeC  As TMP_AttributeC
,TMP1_AttributeD  As TMP_AttributeD
,TMP1_AttributeE  As TMP_AttributeE
,TMP1_Currency    As TMP_Currency
,TMP1_Amount      As TMP_Amount
Resident _Tmp1;

Drop Table _Tmp1;
 
The resulting table counts around 750.000 rows.
 
I posted my solution hoping somebody could suggest a more "clean" solution, and hoping it could be useful for somebody else with similar problem.
 
Thanks for your eventual comments / suggestions
 
Corrado