Help with identifying newly added ID

[Qlikuser225]

Hello All,

Can anyone pls help me with below

I have  a file which loads every week and i need to identify the newly added ID's in the new file and also identify the ID's which were not there in the new file but are in the old file

For Example

Table1 - file received on 12/7

ID , Date

CCC,12/7

DDD,12/7

concatenate

Table1 - file received on 12/1

ID , Date

AAA, 12/1

BBB, 12/1

CCC,12/1

I want to display Below columns on QLik

Newly Added ID - DDD 

Removed ID - AAA  and BBB

Thanks in Advance

[NoahF]

Hi @Qlikuser225 ,

I would combine both tables first and add a Source field, which indicates if the Row is from the Previous File or the Current File.
Then I would Join a distinct count of the source to the combined table. If the Source_count = 1, then the row is only in one file. If the Source_count = 2 the row is in the previous and the current file.

With this modified table we can identify the added rows ( Source_Count = 1 and Source = 'Current') 
and the removed rows (Source_Count = 1 and Source = 'Previous').

// Load Previous Week Table
PreviousWeek:
LOAD * INLINE [
ID, Date
AAA, 12/1
BBB, 12/1
CCC, 12/1
];

// Load Current Week Table
CurrentWeek:
NoConcatenate LOAD * INLINE [
ID, Date
CCC, 12/7
DDD, 12/7
];

CombinedTable:
NoConcatenate LOAD
    ID,
    'Previous' AS Source
RESIDENT PreviousWeek;

CONCATENATE (CombinedTable)
LOAD
    ID,
    'Current' AS Source
RESIDENT CurrentWeek;

//Join the source count on the combined table
left join LOAD
	COUNT(DISTINCT Source) as Count_Source,
    ID
RESIDENT CombinedTable GROUP BY ID;


RemovedID:
Load
	ID as Removed_ID
Resident CombinedTable where Count_Source = 1 and Source = 'Previous';

AddedID:
Load
	ID as Added_ID
Resident CombinedTable where Count_Source = 1 and Source = 'Current';

Drop Table CombinedTable;



Drop tables PreviousWeek, CurrentWeek;

You could also Concatenate the two resulting tables "RemovedID" and "AddedID"

Regards

[Qlikuser225]

Thank you for your reply,

We get two tables in the data model; one is Added and other is Removed.

Can you pls tell me how can we join these two tables (Added and Removed) to the original data (data from all the files) and display what is added and what is removed?

Thank You

[NoahF · Accepted solution]

Not sure if that is what you mean, but you could add the ID to the RemovedID and AddedID tables, in order to join it back on the ID to the CombinedTable.

Left Join (CombinedTable)
RemovedID:
Load
	ID,
	ID as Removed_ID
Resident CombinedTable where Count_Source = 1 and Source = 'Previous';

Left Join(CombinedTable)
AddedID:
Load
	ID,
	ID as Added_ID
Resident CombinedTable where Count_Source = 1 and Source = 'Current';

 
This would be the result (I removed the Source Fields and Count in the final Table to simplify)

With a simple If-Statement you could modify the removed and added columns: 

=if(len(Removed_ID)>0, 'Yes', 'No')

Would look like this then: