topic Re: SQL JOIN query in QlikView

SQL JOIN query

girish_talele — Wed, 25 Nov 2020 16:16:04 GMT

Dear Experts,

In SAP, as shown below, we have following four tables, in which Key field is available in all tables.

And I want to fetch the records of third & fourth table if Creation / Change Date is yesterday’s date in first two tables.

Table	Key Field	Creation Date	Change Date
AFRU	AUFPL	ERSDA
CAUFV	AUFPL	ERDAT	AEDAT
AFVV	AUFPL
AFVC	AUFPL

Existing script is as given below, but it is taking long time to load.

Is there any better way to join tables?

AFRU:
Load AUFPL;
SQL SELECT DISTINCT AUFPL FROM AFRU WHERE ERSDA >= '$(vDate)';

Join (AFRU)
CAUFV:
Load AUFPL;
SQL SELECT AUFPL FROM CAUFV WHERE (CAUFV~ERDAT >= '$(vDate)' OR CAUFV~AEDAT >= '$(vDate)');

LEFT JOIN (AFRU)
AFVV:
Load
AUFPL,
APLZL,
STEUS;
SQL SELECT AFVV~AUFPL AFVV~APLZL AFVC~AUFPL AFVC~APLZL AFVC~STEUS
FROM AFVV INNER JOIN AFVC ON AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL;

Regards,

Girish

Re: SQL JOIN query

maxgro — Sat, 08 Oct 2016 11:00:08 GMT

but it is taking long time to load

which statement?

maybe an option could be to join in SQL the 3 tables using (in SQL) the vDate filter

Re: SQL JOIN query

girish_talele — Sat, 08 Oct 2016 12:20:12 GMT

Dear Maxgro,

Thanks for looking into,

Script is taking long time in Third & Fourth table, there are almost 1.5 Million records loaded (WHERE condition also not applicable),

whereas only 13 K records are matching with key field laded in first & second table. It takes almost 8-10 Min to load,

If I use separate left join i.e. (as given below)

1. Left Join AFRU & AFVV,
2. Left Join CAUFV & AFVV

Then the data is getting fetched in 1 Min only. But I want load the same in one go.

Regards,

Girish.

AFRU:
Load
AUFPL,
APLZL,
STEUS;
SQL SELECT DISTINCT CAUFV~AUFPL
AFVV~AUFPL AFVV~APLZL
AFVC~AUFPL AFVC~APLZL AFVC~STEUS

FROM CAUFV

LEFT JOIN AFVV ON CAUFV~AUFPL = AFVV~AUFPL
INNER JOIN AFVC ON AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL

WHERE (CAUFV~ERDAT >= '$(vLastLoadDate)' OR CAUFV~AEDAT >= '$(vLastLoadDate)');

//--------------------------------------------------------------------------------------------------------------

AFRU:

Load

AUFPL,

APLZL,

STEUS;

SQL SELECT DISTINCT AFRU~AUFPL
AFVV~AUFPL AFVV~APLZL
AFVC~AUFPL AFVC~APLZL AFVC~STEUS

FROM AFRU
LEFT JOIN AFVV ON AFRU~AUFPL = AFVV~AUFPL
INNER JOIN AFVC ON AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL

WHERE ERSDA >= '$(vLastLoadDate)';

Re: SQL JOIN query

Anil_Babu_Samineni — Sat, 08 Oct 2016 12:51:39 GMT

Might this problem you are facing is performance. The reason was you are getting data From different source. I would request you to store this Two table s into qvd and then try to reload. Check whether you will get reduced the elapsed time or not and let me know what's the file size and qvw file size

Re: SQL JOIN query

maxgro — Sat, 08 Oct 2016 12:52:38 GMT

I hope to understand.

If it takes long time in SQL (below), it could be the database and/or the network

One way to reduce (maybe the only one) is to reduce the record Qlik get from the database

SQL SELECT AFVV~AUFPL AFVV~APLZL AFVC~AUFPL AFVC~APLZL AFVC~STEUS
FROM AFVV INNER JOIN AFVC ON AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL;

I think something like (check the syntax)

SQL SELECT AFVV~AUFPL AFVV~APLZL AFVC~AUFPL AFVC~APLZL AFVC~STEUS
FROM AFVV INNER JOIN AFVC ON AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL

WHERE AFVV~AUFPL in

// this should be the same as your first 2 statements (see below, full ouer join ***)

(

SELECT DISTINCT AUFPL FROM AFRU WHERE ERSDA >= '$(vDate)'

UNION

SELECT AUFPL FROM CAUFV WHERE (CAUFV~ERDAT >= '$(vDate)' OR CAUFV~AEDAT >= '$(vDate)')

)

***

AFRU:
Load AUFPL;
SQL SELECT DISTINCT AUFPL FROM AFRU WHERE ERSDA >= '$(vDate)';

Join (AFRU)
CAUFV:
Load AUFPL;
SQL SELECT AUFPL FROM CAUFV WHERE (CAUFV~ERDAT >= '$(vDate)' OR CAUFV~AEDAT >= '$(vDate)');

Re: SQL JOIN query

girish_talele — Mon, 10 Oct 2016 05:54:46 GMT

Dear Massimo,

Thanks again,

I got below error message while running the script.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 Incorrect expression "UNION" in logical condition.)

SQL SELECT AFVV~AUFPL AFVV~APLZL AFVC~AUFPL AFVC~APLZL AFVC~STEUS

FROM AFVV INNER JOIN AFVC ON AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL

WHERE AFVV~AUFPL in

(

SELECT DISTINCT AUFPL FROM AFRU WHERE ERSDA >= '20161008'

UNION

SELECT AUFPL FROM CAUFV WHERE (CAUFV~ERDAT >= '20161008' OR CAUFV~AEDAT >= '20161008')

)

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Modified your script as given below.

AFRU:
Load
AUFPL,
APLZL,
STEUS;
SQL SELECT AFVV~AUFPL AFVV~APLZL AFVC~AUFPL AFVC~APLZL AFVC~STEUS
FROM AFVV INNER JOIN AFVC ON AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL
WHERE AFVV~AUFPL in
(SELECT DISTINCT AUFPL FROM AFRU WHERE ERSDA >= '$(vDate)')

// UNION

OR
AFVV~AUFPL in
(SELECT AUFPL FROM CAUFV WHERE (CAUFV~ERDAT >= '$(vDate)' OR CAUFV~AEDAT >= '$(vDate)'));

*** Instead of Union, I used OR statement & it worked fine.

Although with OR statement I am getting the results, had one query - What was the use of UNION in your script? How to remove the error.

Thanks & regards,

Girish.