tricky exists thing

[Anonymous]

Hi Qlikviewgurus,

I came across many situations where exists behave weird specially in resident.

Can Someone pls explain me why I am not getting the value in tableB

Ideally,I want is matching record from tablea and tablec(temp tables) should be present in tableB,Since I am dropping the table going forward.

TableA:

LOAD * Inline

[

C1

A

B

C

W

];

TableC:

Load * Inline

[

C1,desc

W,hu

A,lo

];

TableB:

LOAD C1 as C1_new

RESIDENT TableA

Where Exists(C1_new,C1);

Drop table TableA;

Drop table TableC;

[sunny_talwar]

Try this:

TableA:

LOAD * Inline

[

C1_new

A

B

C

W

];

TableC:

Load * Inline

[

C1,desc

W,hu

A,lo

];

TableB:

NoConcatenate

LOAD C1_new

RESIDENT TableA

Where Exists(C1, C1_new);

Drop table TableA;

Drop table TableC;

[sunny_talwar]

Forgot to add NoConcatenate to TableC... Fixed now and also attaching the sample

[Anonymous]

Thank you for your instant reply Sunny but its not working.

[sunny_talwar]

Have you looked at the attached application? What else are you expecting to see as the output?

[Anonymous]

Thank you sunny.Can you pls post the script here as PE doesnt allow me to open it.

Actually that comment was before attachment.

[kaushiknsolanki · Accepted solution]

Try this,

TableA:

LOAD * Inline

[

C1

A

B

C

W

];

TableC:

Load * Inline

[

C1,desc

W,hu

A,lo

];

TableB:

LOAD C1 as C1_new

RESIDENT TableA

Where Exists(C1);

Drop table TableA;

Drop table TableC;

To simplify the code you can also try this.

TableC:

Load * Inline

[

C1,desc

W,hu

A,lo

];

TableA:

LOAD * Inline

[

C1

A

B

C

W

]where Exists(C1);

Drop table TableC;

Regards,

Kaushik Solanki

[vinieme12]

IF you want matching records from TABLE A and TABLE C , why are you using exists??

why not INNER JOIN?

[vinieme12]

TableA:

LOAD * Inline

[

C1

A

B

C

W

];

INNER JOIN(TableA)

TableC:

Load * Inline

[

C1,desc

W,hu

A,lo

];

Understanding Join, Keep and Concatenate

[Anonymous]

Hi Kushal, when you are loading TableB, all values of C1 exist on C and A tables, that is because Exists is not working as expected.

Regards!