topic Re: How to get record from a table not present in other table in QlikView

How to get record from a table not present in other table

Mon, 07 Oct 2013 23:44:56 GMT

Hi,

I have two tables. To simplify, table A and table B. The two tables have a field to make a join between them, for example Join_Field.

I want to load data from table 1, and keep only record not present in table B, using Join_Field as criteria.

Example:

Table A

---------------------------------------

Field A1, Field A2, Join_Field

A B 1

C D 2

Table B

-----------------------------------------

Field B1, Field B2, Join Field

W X 1

Y Z 1

RESULT

----------------------------------------------------------------------------------

Field A1, Field A2, Join Field Field B1 Field B2

C D 2 blank blank

If fields B1 and B2 cannot be get in result, it does not matter (I can fill manually in a later step)

How can I do it??

Regards.

Re: How to get record from a table not present in other table

MayilVahanan — Tue, 08 Oct 2013 05:14:28 GMT

Try with

Not Exists() Function

Load * from tableA where not exsists (JoinField, JoinFieldofB);

Edit:

I think not exists does not solve for your requirement, try like this

Test:

LOAD * inline [

FieldA1, FieldA2, JoinField

A,B,1

C,D,2

];

Outer Join(Test)

LOAD * Inline [

FieldB1,FieldB2,JoinField

W,X,1

Y,Z,1

];

NoConcatenate

LOAD * Resident Test Where IsNull(FieldB2);

DROP Table Test;

Hope that helps

Re: How to get record from a table not present in other table

Tue, 08 Oct 2013 05:36:59 GMT

Pleas refer the following application

Re: How to get record from a table not present in other table

Tue, 08 Oct 2013 06:07:30 GMT

Hi,

PFA

Re: How to get record from a table not present in other table

Tue, 08 Oct 2013 08:02:09 GMT

Hi Venkat,

Thanks. I have tried with your example and Works, but I have a problema when trying with my actual tables.

The problem is because join field has not the same name in both tables.

How do I have to modify your solution if fields have the following names??

Table A

Field A1, Field A2, LOCATOR

Table B

Field B1, Field B2, LOCALIZADOR

I have tried several ways (using AS LOCATOR, and so on), but field is not recognized.

Thanks a lot for your help.

Regards

Re: How to get record from a table not present in other table

Tue, 08 Oct 2013 09:05:30 GMT

Sorry again.

I have solved this problem, but I have another one. I get the Table with the results, following your instructions. But when I also add to the script the load of origin tables (I also need to keep origin tables with a join), qlikview hangs.

In fact I need:

- Records of table A present in table B, filtered by table B criteria

- Record of table A not present in table B (your solution)

How can I have both in same script?

Re: How to get record from a table not present in other table

Tue, 08 Oct 2013 13:27:13 GMT

Hi again,

I have solved. I forget an unqualify sentence before join, and several syn were being created.

Regards