topic Re: tricky exists thing in QlikView

tricky exists thing

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

[

];

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;

Re: tricky exists thing

sunny_talwar — Thu, 18 Aug 2016 00:46:08 GMT

Try this:

TableA:

LOAD * Inline

[

C1_new

];

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;

Re: tricky exists thing

sunny_talwar — Thu, 18 Aug 2016 00:49:32 GMT

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

Re: tricky exists thing

Anonymous — Thu, 18 Aug 2016 00:55:23 GMT

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

Re: tricky exists thing

sunny_talwar — Thu, 18 Aug 2016 01:03:56 GMT

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

Re: tricky exists thing

Anonymous — Thu, 18 Aug 2016 01:37:01 GMT

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

Actually that comment was before attachment.

Re: tricky exists thing

kaushiknsolanki — Thu, 18 Aug 2016 04:52:23 GMT

Try this,

TableA:

LOAD * Inline

[

];

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

[

]where Exists(C1);

Drop table TableC;

Regards,

Kaushik Solanki

Re: tricky exists thing

vinieme12 — Thu, 18 Aug 2016 05:48:57 GMT

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

why not INNER JOIN?

Re: tricky exists thing

vinieme12 — Thu, 18 Aug 2016 05:49:44 GMT

TableA:

LOAD * Inline

[

];

INNER JOIN(TableA)

TableC:

Load * Inline

[

C1,desc

W,hu

A,lo

];

Understanding Join, Keep and Concatenate

Re: tricky exists thing

Anonymous — Thu, 18 Aug 2016 06:38:39 GMT

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!

Re: tricky exists thing

sunny_talwar — Thu, 18 Aug 2016 08:16:04 GMT

It is this script:

TableA:

LOAD * Inline

[

C1_new

];

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;

Output I get is this:

Re: tricky exists thing

Anonymous — Thu, 18 Aug 2016 13:02:22 GMT

Hi Manuel,Can you please explain me in detail what u meant.

Table A comprises of A,B,C,W values for C1 Column.

Table C comprises of A,W values for C1 Column.

when table B starts executing it should make C1 column of Table to C1_new column and compare the existing values with the C1 which should give me the result.

Pls correct my understandiing and logic.

Re: tricky exists thing

Anonymous — Thu, 18 Aug 2016 13:16:58 GMT

TableA:

LOAD * Inline

[

];

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;

this approach records all the data in table B but our condition is just matching records.Kindly correct me