Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

SQL JOIN query

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

Tags (2)
5 Replies
Highlighted
MVP
MVP

Re: SQL JOIN query

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

Highlighted
Creator
Creator

Re: SQL JOIN query

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. 1. Left Join AFRU & AFVV,
  2. 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)';

Highlighted

Re: SQL JOIN query

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
MVP
MVP

Re: SQL JOIN query

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)');



Highlighted
Creator
Creator

Re: SQL JOIN query

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.