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.

markgraham123
Valued Contributor

added and removed IDs from 2 tables

Hi all,

I'm working on a req. where i have 2 tables:

Table1:                        Table2:                                     

ID, Name                 ID, Name            

1,A                                1,A

2,B                                 2,B

3,C                                 3,C

4,D                                 6,F

5,E                                  7,G

     In the output i want to see

newly added records as :

ID, Name

6,F

7,G

Removed records as:

ID, Name

4,D

5,E

Tags (2)
1 Solution

Accepted Solutions

Re: added and removed IDs from 2 tables

Its the Where Exists statement which checks for a value in another field elsewhere loaded before this table's load. So ID comes from Table1 and it's checking against ID1 -> Where not Exists(ID, ID1)

6 Replies

Re: added and removed IDs from 2 tables

You only want to see the new and old? and get rid of the other stuff or do you still want to be able to keep all the things?

markgraham123
Valued Contributor

Re: added and removed IDs from 2 tables

Sunny,

I want to see all the Table1 records and Table2 records.

In the end, i need New and old records in separate fields.

Re: added and removed IDs from 2 tables

May be this:

Table1:

LOAD * Inline

[

ID, Name

1,A

2,B

3,C

4,D

5,E

];

TempTable2:

NoConcatenate

LOAD ID as ID1,

  Name

Inline

[

ID, Name

1,A

2,B

3,C

6,F

7,G

];

Table3:

LOAD ID1 as AddedID,

  Name as AddedName

Resident TempTable2

Where not Exists(ID, ID1);

Table4:

LOAD ID as RemovedID,

  Name as RemovedName

Resident Table1

Where not Exists(ID1, ID);

Table2:

NoConcatenate

LOAD ID1 as ID,

  Name

Resident TempTable2;

DROP Table TempTable2;

markgraham123
Valued Contributor

Re: added and removed IDs from 2 tables

Sunny,

This is what i'm exactly looking for...

I have a question here..

While doing the resident load in Table3 from resident TempTable2.....i do not see any filed ID in the TempTable2 table.

But how are we specifiying in where condition...

Can you please tell.

Re: added and removed IDs from 2 tables

Its the Where Exists statement which checks for a value in another field elsewhere loaded before this table's load. So ID comes from Table1 and it's checking against ID1 -> Where not Exists(ID, ID1)

markgraham123
Valued Contributor

Re: added and removed IDs from 2 tables

Perfecto Sunny !!