Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

chesterluck
Contributor II

Load with sql join over two tables of the same datasouce

Hello @all

I want to reload a report every 5 min. In order not to overload the datasource I thought of doing  sql query on the data.

My idea is to do sth like this:

Load*;

SQL Select

Table1.Field1,

Table1.Field2,

Table2.Field1,

Max(Table2.Field2) as Tbl2F2

From ds1.Table1, ds1.Table2

Where Table1.Field1=Table2.Field1

Group By Table1.Field1, Table1.Field2, Table2.Field1;

Is this possible and if yes where is the difference in the performance to the following code?

Load*;

SQL Select

Field1,

Max(Field2) as Tbl2F2

From  ds1.Table2

Group By Table2.Field1;

Left Join

Load*;

SQL Select

Field1,

Field2

From  ds1.Table1;

Tags (3)
1 Reply
chesterluck
Contributor II

Re: Load with sql join over two tables of the same datasouce

MAX_MAC_STA:

Load *;
SQL SELECT
    lJobRef,
    lMacRef,
    Max(lMacStaRef) as [letzter Status]
   
FROM TIG1.dbo."MAC_STA"
Group By lMacRef, lJobRef;

Outer Join

Load *;
SQL SELECT
    lJobRef,
    lMacRef,
    Max(lMacStaRef) as [letzter Status]
   

FROM TIG1.dbo."MAC_STA"
Where (iMState In (2,5))
Group By lMacRef, lJobRef;

  

left join

Load *;
SQL SELECT dActShots as [Schussmenge Status Ist],
    iMState,
    iPState,
    lMacStaRef as [letzter Status],
    tBegin as [Status Beginn],
    tEnd as [Status Ende]

FROM TIG1.dbo."MAC_STA";


left Join

LOAD *;
SQL SELECT dActShots as [Schussmenge Job Ist],
    dActSpeed as Zykluszeit,
    dSetShots as [Schussmenge Job Soll],
    lJobRef,
    sName as Auftrag,
    sType,
    tActBegin as Auftragsbeginn,
    tActEnd as Auftragsende
FROM TIG1.dbo.JOB;

Maybe you can help me with this code. My aim is to do as much as possible on the db side. I dont want to load a whole table in order to do joins on QV side.

I wanted first to do a join between a resident table and a db, but these a two different "source" systems...

The code is working fine like this, but as i said it probably loads to much data in qv. And as I have to repeat this every 5 min , I dont want to load too much

Thx in advance. chesterluck

Community Browser