Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Special join

Hello everyone,

we have 2 tables:

tableNewNumbers:

numberstate
123active
234active

tableOldNumbers:

numberstate
234deleted
888active

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

Our result table should be:

numberstate
123active
234

active

888removed
1 Solution

Accepted Solutions
jason_michaelid
Not applicable

Special join

Data:

LOAD

     Number

     ,State

FROM Table1....;

CONCATENATE (Data)

LOAD

     Number

     ,'removed'     AS     State

FROM Table2

WHERE NOT EXISTS(Number);

Hope this helps,

Jason

8 Replies
jason_michaelid
Not applicable

Special join

Data:

LOAD

     Number

     ,State

FROM Table1....;

CONCATENATE (Data)

LOAD

     Number

     ,'removed'     AS     State

FROM Table2

WHERE NOT EXISTS(Number);

Hope this helps,

Jason

Not applicable

Re: Special join

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:

numberstate
123active
234active
234deleted
888active
Not applicable

Re: Special join

Hi Sleepa,

   what  Jason Michaelides suggested is working fine .

Kindly find the attached application.

Regards,

Sampath Kumar G

jason_michaelid
Not applicable

Special join

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

Not applicable

Re: Special join

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.

Not applicable

Re: Special join

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.

Not applicable

Special join

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.

jason_michaelid
Not applicable

Special join

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