topic Re: Problem in joining table in QlikView

Problem in joining table

Tue, 04 Sep 2012 06:19:17 GMT

I m loading data from QVDs can anyone tell me how i can implement below SQL Query in QlikView Load statement using QVDs.

Select * from Table1, Table2 where Table1.field1 = Table2.field2 and Table1.datetime between (sysdate - 60) AND sysdate.

Re: Problem in joining table

whiteline — Tue, 04 Sep 2012 06:24:43 GMT

There are two tables in the query. Could you add some info what table you are trying to load and what is already loaded ?

Re: Problem in joining table

Tue, 04 Sep 2012 06:30:56 GMT

I m willing to Load all fields from these two table.

i have tables like

Table1:

Field1 Field2 Field3 Field4

Table2:

Field2 Field5 Field6 Field7

and required out is

ResultantTable:

Field1 Field2 Field3 Field4 Field5 Field6 Field7

Re: Problem in joining table

whiteline — Tue, 04 Sep 2012 06:44:30 GMT

Ok.

In QlikView you can't load from more than one table at a time.

So you have to do it step by step, for example.

Result:

Load * from 1.qvd (qvd)

where datetime<Today() and datetime>Today()-60;

left join(Result)

Load * from 2.qvd (qvd)

where exists('field1', field2);

Re: Problem in joining table

Tue, 04 Sep 2012 07:03:27 GMT

Another thing can you please tell me, is there any way to use subquery or any trick so that i can use subquries in LOAD statement.

Lets suppose my query is:

Select Field1, Field2, (Select Field3 From table2 t2 where t2.Field3 = t1.Field1) From Table1 t1

Re: Problem in joining table

whiteline — Tue, 04 Sep 2012 07:18:34 GMT

No, you have to do them step by step.

Athough, you can always use some tricks as 'distinct', exists() and 'keep' (look at help).

Also there is incremental LOAD (read it from bottom to top, the number of rows is the same):

[New Table]:

LOAD

Field1

Field2,

SomeFoo(Field3) as Field4

SomeOtherFoo(Field3) as Field5;

LOAD

Field1,

Field2,

Field3

Resident [SomeTable]

where [Some Complex condition];

Re: Problem in joining table

Tue, 04 Sep 2012 10:44:49 GMT

cant understand what exactly you are telling in this example can you please give another example?

Re: Problem in joining table

whiteline — Tue, 04 Sep 2012 10:48:30 GMT

The above example is equivalent to these two loads:

[Temporary table]:

LOAD

Field1,

Field2,

Field3

Resident [SomeTable]

where [Some Complex condition];

[New Table]:

Noconcatenate

LOAD

Field1

Field2,

SomeFoo(Field3) as Field4

SomeOtherFoo(Field3) as Field5

Resident [Temporary table];

drop table [Temporary table];