compare two table fields

[#1]

Hi All,

Table 1:

ServiveName	Installation Date	Expiry Date	StartDate	EndDate	status
ABC	1/1/2011	1/1/2012	2/2/2011	5/5/2011	both
BCD	3/3/2014	1/1/2015	2/4/2014	12/12/2015	both
MNC	2/2/2012	1/4/2013	3/3/2012	5/5/2012	both
MNO			3/3/2000	12/5/2000	System2
PQR	1/4/2011	12/5/2011	5/5/2011	6/6/2011	both
SPR			1/2/2009	1/2/2010	System2
XYZ	2/2/2011	2/2/2013			System1

Table 2:

ServiveName	Installation Date	Expiry Date	StartDate	EndDate	Status
XYZ	2/2/2011	2/2/2014
BCD	3/3/2014	1/1/2015	2/4/2014	12/12/2015

Note : key field is ServiveName

compare these two tables,if any record change in Table 1 that record update in the Table 2 and flag the status as "update" in table 2

                                      if any record not exist in Table 1 and exist in Table 2 flag the status as "delete" in table 2 

                                      if any record exist in Table 1 and not exist in Table 2 ,insert that record into table 2 and status as "Insert" in table 2

Thanks in advance

[jagan · Accepted solution]

Hi Suresh,

PFA attached file for solution.  Following script is used in the file.

Table1:

LOAD * INLINE [
ServiveName, Installation Date, Expiry Date, StartDate, EndDate, status
ABC, 1/1/2011, 1/1/2012, 2/2/2011, 5/5/2011, both
BCD, 3/3/2014, 1/1/2015, 2/4/2014, 12/12/2015, both
MNC, 2/2/2012, 1/4/2013, 3/3/2012, 5/5/2012, both
MNO, , , 3/3/2000, 12/5/2000, System2
PQR, 1/4/2011, 12/5/2011, 5/5/2011, 6/6/2011, both
SPR, , , 1/2/2009, 1/2/2010, System2
XYZ, 2/2/2011, 2/2/2013,,, System1];


Table2Temp:
LOAD * INLINE [
ServiveName, Installation Date, Expiry Date, StartDate, EndDate, Status
XYZ, 2/2/2011, 2/2/2014
BCD, 3/3/2014, 1/1/2015, 2/4/2014, 12/12/2015
AAA, 3/3/2014, 1/1/2014];

LEFT JOIN (Table2Temp)
LOAD
ServiveName AS ServiveName,
[Installation Date] AS T1InstallationDate,
[Expiry Date] AS T1ExpiryDate,
[StartDate] AS T1StartDate,
[EndDate] AS T1EndDate,
status AS T1Status
RESIDENT Table1;

Table2:
LOAD
ServiveName AS T2SerivceName,
ServiveName,
If(T1InstallationDate <> [Installation Date], T1InstallationDate, [Installation Date]) AS T2InstallationDate,
If(T1ExpiryDate <> [Expiry Date], T1ExpiryDate, [Expiry Date]) AS T2ExpiryDate,
If(T1StartDate <> [StartDate], T1StartDate, [StartDate]) AS T2StartDate,
If(T1EndDate <> [EndDate], T1EndDate, [EndDate]) AS T2EndDate,
If(IsNull(T1Status), 'New',
If(T1InstallationDate <> [Installation Date] OR T1ExpiryDate <> [Expiry Date] OR T1StartDate <> [StartDate] OR T1EndDate <> [EndDate], 'Update', 'No Change')) AS T2Status
RESIDENT Table2Temp;

Concatenate(Table2)
LOAD
ServiveName,
[Installation Date] AS T2InstallationDate,
[Expiry Date] AS T2ExpiryDate,
StartDate AS T2StartDate,
EndDate AS T2EndDate,
'Insert' AS T2Status
RESIDENT Table1
WHERE NOT EXISTS(T2SerivceName, ServiveName);
DROP TABLE Table2Temp;

Regards,

Jagan.

[#3]

thank you for reply,

i am  getting duplicate records

please find below  result table

ID2ServiveName	ID2InstallationDate	ID2ExpiryDate	ID2StartDate	ID2EndDate	ID2Status
ABC	1/1/2011	1/1/2012	2/2/2011	5/5/2011	INSERT
BCD	3/3/2014	1/1/2015	2/4/2014	12/12/2015	INSERT
BCD	3/3/2014	1/1/2015	2/4/2014	12/12/2015	No Change
MNC	2/2/2012	1/4/2013	3/3/2012	5/5/2012	INSERT
MNO			3/3/2000	12/5/2000	INSERT
PQR	1/4/2011	12/5/2011	5/5/2011	6/6/2011	INSERT
SPR			1/2/2009	1/2/2010	INSERT
XYZ	2/2/2011	2/2/2013			INSERT

Regards

suresh

[jagan]

Hi Suresh,

Please check attached file, I am not getting any duplicates.

Regards,

Jagan.

[#5]

Hi jaganmohan,

That file is not open in my machine.

Regards.

Suresh

[jagan]

Hi Suresh,

Just load this script and add all dimensions in Table2 in Table box and check.  This is what I did and am not getting any duplicates.

Regards,

Jagan.

[#7]

Thank you JaganMohan,