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.

Not applicable

where exists

Hello

I have two tables that are concatenated and build tableAB:

tableA

tableB

I have three others:

tableC

tableD

tableE

tableC is the parent and is inner joined to both  tableD, tableE

I would like tableC to only contain records found in tableAB (inner join).

I've tried where exists: Load tableC where exists(tableAB.column,tableC.column) but the scripting results report 0 records from tableC, however tableD and tableE still get loaded.

Can someone tell me where Im going wrong and how D and E load while inner joined to a table that apparently has zero records in it?

Thank you.

4 Replies
maxgro
Not applicable

Re: where exists

Please post the script, thx

effinty2112
Not applicable

Re: where exists

Try

(this is pseudocode)

C:

Left Keep(AB)

Load

*

From SourceTableC;

AB is a resident table and you want to create a new resident table C. In a Left Keep script like this the data from SourceTableC is reduced down to the rows with that match those in AB in the fields of the same name. Like a Join but the tables remain separate in the data model.

An exists clause is good but only operates on a pair of fields (one in each of the tables that can have different names). Keep can operate on many pairs of like named fields.

In the scenario above a Left Keep will reduce data coming from the source. A Right Keep (not so common) will bring in all records from the source but reduce the resident table to the matching rows. An Inner Keep reduces both to their matching rows.

If you're thinking that keep is just like a join then you're dead right. But there is that big difference - the tables remain separate in the model.

swuehl
Not applicable

Re: where exists

Are you qualifying your tables?

Then I think you need to take care of the field name in the EXISTS() since the second qualify has not happened when loading in the data:

QUALIFY *;

TableAB:

LOAD * INLINE [

Column

1

2

3

];

TableC:

LOAD * INLINE [

Column

3

5

6

]

WHERE EXISTS(TableAB.Column, Column)

;

Not applicable

Re: where exists

thank you for your replies, I have something to go with now.