topic Re: How to apply Where clause after multiple left join ? in QlikView

How to apply Where clause after multiple left join ?

Thu, 22 May 2014 19:00:03 GMT

Hi,

I am new to qlikview.

I have below questions.

How we can apply to Where caluse to entire results ?

I want to apply where caluse to the results we get after multiple joins ..

Example :

Noconcatanate:

Test:

LOAD *

Resident Table1: <---1000 records

Left Join

LOAD *

Resident Table2; <---300 records

Left Join

LOAD *

Resident Table3 <---Total 200 records among that 100 records has field 'abc'

Where Table3.field = 'abc'

so, end results , i was expecting to be 100 records ,

but, still getting 1000 records.

it looks like WHERE clause is applying to Table3 only and due to left join,

it returns 1000 reords and WHERE condition is not considering.

basically,

I want to apply that WEHRE clause entire results.

Re: How to apply Where clause after multiple left join ?

Anonymous — Thu, 22 May 2014 20:04:07 GMT

Use inner join:

Test:

LOAD *

Resident Table1: <---1000 records

Left Join (Test)

LOAD *

Resident Table2; <---300 records

Inner Join (Test)

LOAD *

Resident Table3 <---Total 200 records among that 100 records has field 'abc'

Where Table3.field = 'abc'

Re: How to apply Where clause after multiple left join ?

jpapador — Thu, 22 May 2014 20:20:25 GMT

You could also try storing (or a resident load) the table after you've completed the joins:

Noconcatanate:

Test:

LOAD *

Resident Table1: <---1000 records

Left Join

LOAD *

Resident Table2; <---300 records

Left Join

LOAD *

Resident Table3 <---Total 200 records among that 100 records has field 'abc'

Where Table3.field = 'abc'

Test2:

Noconcatenate LOAD *

Resident Test

Where Field = 'abc'

Drop Table Test;