- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
[TEMP]:
LOAD
Key,
ValueA
......;
LEFT JOIN ([TEMP])
LOAD
Key,
ValueB
......;
[TABLE]:
NOCONCATENATE LOAD
*
RESIDENT [TEMP]
WHERE ValueA>ValueB;
DROP TABLE [TEMP];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
[TEMP]:
LOAD
Key,
ValueA
......;
LEFT JOIN ([TEMP])
LOAD
Key,
ValueB
......;
[TABLE]:
NOCONCATENATE LOAD
*
RESIDENT [TEMP]
WHERE ValueA>ValueB;
DROP TABLE [TEMP];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
// ***************************************************************************
// * 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;