Comparing 2 Datasets and find 3 different values

Karthick30 — Thu, 21 Jul 2022 19:03:43 GMT

I have 2 Data set. this month file and previous month file. Both files has same column names. Server name is the Primary Key for both the excels.

Now I need to compare this month file with previous month file and find out

1)how many servers newly present in this month file(not exist in previous month file) and mark is as new server count

2)how many servers unchanged between previous month file and this month file and mark it as unchanged server count

3)how many servers exist in previous month file and not exist in this month file and mark it as Remediated server count.

Any logic to achieve this

Re: Comparing 2 Datasets and find 3 different values

maxgro — Thu, 21 Jul 2022 20:12:12 GMT

// test data

ThisMonth:
LOAD * inline [
Server
1
2
3
4
5
];

PrevMonth:
NOCONCATENATE LOAD * inline [
Server
4
5
6
7
];

Compare:
LOAD *, 1 as ThisMonth RESIDENT ThisMonth;
JOIN (Compare) LOAD *, 1 as PrevMonth RESIDENT PrevMonth;

LEFT JOIN (Compare) LOAD
Server,
IF(ThisMonth AND PrevMonth, 'Unchanged',
IF(ThisMonth, 'New',
'Remediated')) AS Status
RESIDENT Compare;