topic Re: added and removed IDs from 2 tables in QlikView

added and removed IDs from 2 tables

markgraham123 — Thu, 10 Mar 2016 15:27:44 GMT

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

Re: added and removed IDs from 2 tables

sunny_talwar — Thu, 10 Mar 2016 15:35:14 GMT

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?

Re: added and removed IDs from 2 tables

markgraham123 — Thu, 10 Mar 2016 15:37:13 GMT

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

sunny_talwar — Thu, 10 Mar 2016 15:45:16 GMT

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;

Re: added and removed IDs from 2 tables

markgraham123 — Thu, 10 Mar 2016 20:38:01 GMT

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

sunny_talwar — Thu, 10 Mar 2016 20:55:15 GMT

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)

Re: added and removed IDs from 2 tables

markgraham123 — Thu, 10 Mar 2016 21:01:46 GMT

Perfecto Sunny !!