"Merge records of two same-structure tables in the original order by one or more fields and find .."

mars RaqForum 25 No.
191 View • 1 Years ago

3.21 Merge same-order sequences in the original order and calculate difference

Merge records of two same-structure tables in the original order by one or more fields and find their difference as needed. One instance is to find the newly-increased records, deleted records and updated records respectively based on two transaction information tables of different versions – new.xlsx and old.xls.

old.xlsx:

UserName	Date	SaleValue	SaleCount
Rachel	2015-03-01	4500	9
Rachel	2015-03-03	8700	4
Tom	2015-03-02	3000	8
Tom	2015-03-03	5000	7
Tom	2015-03-04	6000	12
John	2015-03-02	4000	3
John	2015-03-02	4300	9
John	2015-03-04	4800	4

new.xlsx:

UserName	Date	SaleValue	SaleCount
Rachel	2015-03-01	4500	9
Rachel	2015-03-02	5000	5
Ashley	2015-03-01	6000	5
Rachel	2015-03-03	11700	4
Tom	2015-03-03	5000	7
Tom	2015-03-04	6000	12
John	2015-03-02	4000	3
John	2015-03-02	4300	9
John	2015-03-04	4800	4

SPL script:

	A
1	=T(“old.xlsx”)
2	=T(“new.xlsx”)
3	=A1.sort(UserName,Date)
4	=A2.sort(UserName,Date)
5	=new=[A4,A3].merge@d(UserName,Date)
6	=delete=[A3,A4].merge@d(UserName,Date)
7	=diff=[A4,A3].merge@d(UserName,Date,SaleValue,SaleCount)
8	=update=[diff,new].merge@d(UserName,Date)
9	return [new, delete, update]

A1 Retrieve old data table.
A2 Retrieve new data table.
A3 Sort A1 by UserName and Date.
A4 Sort A2 by UserName and Date.
A5 merge function works with @d function to perform the order-based merge and remove records also belong to A3 from A4 and return a sequence of newly-increased records.
A6 merge function works with @d function to perform the order-based merge and remove records also belong to A4 from A3 and return a sequence of deleted records.
A7 merge function works with @d function to perform the order-based merge and remove A3’s modified records (values of certain fields have been changed) from A4.
A8 merge function works with @d function to perform the order-based merge and remove newly-increased records from the modified records and return a sequence of updated records.
A9 Return a sequence containing newly-increased records, deleted records and updated records.

Execution result:

imagepng