Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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

View solution in original post

8 Replies
Highlighted
Partner
Partner

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

View solution in original post

Highlighted
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
Highlighted
Not applicable

Re: Special join

Hi Sleepa,

   what  Jason Michaelides suggested is working fine .

Kindly find the attached application.

Regards,

Sampath Kumar G

Highlighted
Partner
Partner

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

Highlighted
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.

Highlighted
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.

Highlighted
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.

Highlighted
Partner
Partner

Special join

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