Load with sql join over two tables of the same datasouce

Anonymous — Mon, 15 Apr 2013 14:21:13 GMT

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;

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

Anonymous — Tue, 16 Apr 2013 07:12:39 GMT


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

topic Load with sql join over two tables of the same datasouce in QlikView

Load with sql join over two tables of the same datasouce

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