topic Special join in QlikView

Special join

Fri, 20 Apr 2012 08:12:46 GMT

Hello everyone,

we have 2 tables:

tableNewNumbers:

number	state
123	active
234	active

tableOldNumbers:

number	state
234	deleted
888	active

The state of all numbers in "tableOldNumbers" which do not exist in "tableNewNumbers" should be changed to 'removed'.

Our result table should be:

number	state
123	active
234	active
888	removed

Special join

Jason_Michaelides — Fri, 20 Apr 2012 08:39:58 GMT

Data:

LOAD

Number

,State

FROM Table1....;

CONCATENATE (Data)

LOAD

Number

,'removed' AS State

FROM Table2

WHERE NOT EXISTS(Number);

Hope this helps,

Jason

Re: Special join

Fri, 20 Apr 2012 08:57:56 GMT

Thanks for your quick answer.

This was also our idea, but the field "state" has already a value in Table2 which has to be updated.

The result of your advice is:

number	state
123	active
234	active
234	deleted
888	active

Re: Special join

Fri, 20 Apr 2012 09:18:23 GMT

Hi Sleepa,

what Jason Michaelides suggested is working fine .

Kindly find the attached application.

Regards,

Sampath Kumar G

Special join

Jason_Michaelides — Fri, 20 Apr 2012 09:27:41 GMT

Are you sure you are loading tableNewNumbers first? To use your exact table names in my script:

Data:

LOAD

Number

,State

FROM tableNewNumbers....;

CONCATENATE (Data)

LOAD

Number

,'removed' AS State

FROM tableOldNumbers

WHERE NOT EXISTS(Number);

If 234 existis already in tableNewNumbers it will not be loaded from tableOldNumbers. Likewise, if 888 does not exist in tableNewNumbers then it will be loaded from tableOldNumbers.

Jason

Re: Special join

Fri, 20 Apr 2012 09:38:43 GMT

Slight modification. If in the real world you are trying to find out how to join two tables because you are pulling in some different fields then you can do this. Otherwise, if the tables are the same then Jason's way is fine.

Data:

LOAD

Number

,State

FROM Table1....;

LEFT JOIN LOAD

Number

,State

,SomeOtherField

FROM Table2;

CONCATENATE (Data)

LOAD

Number

,'removed' AS State

,SomeOtherField

FROM Table2

WHERE NOT EXISTS(Number);

Seems pretty strange wanting to join on state though, maybe this isn't what you're really trying to do.

Re: Special join

Fri, 20 Apr 2012 09:47:31 GMT

Hi Sleepa,

What Jason and Sampath said is right. I also tried that and i got the perfect output as per yours. Check it out once again.

Regards,

Arun Prasadh.N.

Special join

Fri, 20 Apr 2012 11:09:42 GMT

THANKS!!!

Our problem was that we got a different result when Table2 was already loaded.

Table2:

LOAD Number,

State

From Table2;

Data:

LOAD Number,

State

FROM Table1;

CONCATENATE

LOAD Number,

'removed' as State

RESIDENT Table2

WHERE NOT EXISTS(Number);

Now we concatenate while loading Table2 the first time. Then it is correct.

Special join

Jason_Michaelides — Fri, 20 Apr 2012 11:24:36 GMT

Yes - EXISTS checks back for that field in any previous section of the script.