where exists help

[#1]

Hi, i have this situation::

TableA:

number    nameA

1    aaaa

2    bbbb

3    cccc   

4    dddd

5    ffff

6    gggg

tableB:

number    nameB

1    XXXXX

2    YYYYY

5    RRRRR

I want to show in qlikview TableA and TableB, but in TableB only the rows that number from TableB exist in TableA. The way it is now, it shows:

1    XXXXX

2    YYYYY

3    -

4    -

5    RRRRR

6    -

How can I do that? Is there a way to do without create a 3rd table ? maybe with "where exists", but how?

thanks

[jonathandienst]

Hi

Not sure I understand, but if you have a straight table dimensioned with the field "number", then the expression =Only(nameB) will display the way you show in your example.

If you want to show the nulls, then ensure that the suppress zero and suppress null options are off.

Regards

Jonathan

[Gabriel]

HI,

I think best option is to use LEFT KEEP, meaning Table B will only show common fields.

TableA:

number    nameA

1    aaaa

2    bbbb

3    cccc  

4    dddd

5    ffff

6    gggg

tableB:

LEFT KEEP  (TableA)

number    nameB

1    XXXXX

2    YYYYY

5    RRRRR

Hope this is helpful?

Best Regards,

Gabriel

[pgrenier · Accepted solution]

Holà Juliano,

This is what you can do to accomplish this:

Regards,

Philippe

[CELAMBARASAN]

Swap the order and use left keep.

tableB:

number    nameB

1    XXXXX

2    YYYYY

5    RRRRR

Left Keep(tableB)

TableA:

number    nameA

1    aaaa

2    bbbb

3    cccc  

4    dddd

5    ffff

6    gggg

[#6]

Thank you all, Philippe Grenier answer is what I was looking for.