topic Re: How to join with filtering based on both tables in QlikView

How to join with filtering based on both tables

Thu, 23 Jul 2009 19:44:51 GMT

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.

How to join with filtering based on both tables

Thu, 23 Jul 2009 20:27:47 GMT


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

Re: How to join with filtering based on both tables

cvillani — Wed, 02 Aug 2023 10:04:07 GMT

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

Re: How to join with filtering based on both tables

BrunPierre — Fri, 04 Aug 2023 09:34:24 GMT

@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.

Re: How to join with filtering based on both tables

cvillani — Fri, 04 Aug 2023 14:25:32 GMT

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):

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&'%')

Exists(TMP_AccountAttrA, Account&AttributeA)

) And (

Exists(TMP_AccountAttrB,Account&'%')

Exists(TMP_AccountAttrB, Account&AttributeB)

) And (

Exists(TMP_AccountAttrC,Account&'%')

Exists(TMP_AccountAttrC, Account&AttributeC)

) And (

Exists(TMP_AccountAttrD,Account&'%')

Exists(TMP_AccountAttrD, Account&AttributeD)

) And (

Exists(TMP_AccountAttrE,Account&'%')

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

Re: How to join with filtering based on both tables

cvillani — Sun, 06 Aug 2023 15:36:39 GMT

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