Incremental Load -- Identify type

[katetsan]

Hi Communities,

I would like to extract the modified data only between Table A and Table  by using incremental load. In addition, I would like to  identify the data, and create a new column called "Modified Type", which includes "Add", "Modified" and "Deleted". (

Please refer below information.

Thanks in advanced.

Table A:

Order Number	Product ID	Sales Number	Unit Price	Sales Amount	Order Date
40838	910	71	325	23075	2017/1/26
40088	789	12	456	5472	2017/2/1
40874	90	65	347	22555	2017/6/12
40970	242	62	114	7068	2017/2/6
40204	548	1	33	33	2017/4/9
40001	210	56	282	15792	2017/6/29
40405	699	70	453	31710	2017/4/18
40905	820	90	211	18990	2017/2/25
40845	31	37	255	9435	2017/4/24
40807	768	44	332	14608	2017/2/16
40759	48	33	301	9933	2017/6/14

Table B:

Order Number	Product ID	Sales Number	Unit Price	Sales Amount	Order Date
40838	910	71	325	23075	2017/1/26
40088	789	12	456	5472	2017/2/1
40874	90	75	347	26025	2017/8/12
40970	242	82	114	9348	2017/7/14
40204	548	1	33	33	2017/4/9
40001	210	56	282	15792	2017/6/29
40405	699	70	453	31710	2017/4/18
40905	820	90	211	18990	2017/2/25
40807	768	44	332	14608	2017/2/16
40759	48	33	301	9933	2017/6/14
40978	31	21	255	5355	2017/7/13

Output:  

Order Number	Product ID	Sales Number	Unit Price	Sales Amount	Order Date	Modified Type
40874	90	75	347	26025	2017/8/12	Modified
40970	242	82	114	9348	2017/7/14	Modified
40845	31	37	255	9435	2017/4/24	Deleted
40978	31	21	255	5355	2017/7/13	Add

[shiveshsingh]

Why do you want to use incremental load on this?

Which field do you wamt to keep reference for incremental load?

You can simple use where not exist(Table_name, Field_name)

[yadhunandhanan]

Hi Kate Tsan,

Use attached qvw file. Hope it helps.

Thanks,

Yadhu

[katetsan]

Hi Sirs,

Using join expression to compare two tables, but not sure the efficiency with big data set.

Thanks for your advised.

//************************* Identify "Add" or "Modified" ************************* //

[Add Table]:

LOAD

[Order Number],

     [Product ID],

     [Sales Number],

     [Unit Price],

     [Sales Amount],

     [Order Date]

FROM [Data\Incremental Reload.xlsx] (ooxml, embedded labels, table is 工作表2)

Where [Order Date]>= '2017/07/01'

;

[Initial Table]:

Left Join Load

[Order Number],

'A' as Temp

From [Output\qvd files\IncrementalReload_Initial_P2.QVD](qvd)

;

[Final_Add Table]:

Load

[Order Number],

     [Product ID],

     [Sales Number],

     [Unit Price],

     [Sales Amount],

     [Order Date],

if(Temp = 'A' and [Order Date]>= '2017/07/01', 'Modified', 'Add') as [Modified Type]

Resident [Add Table]

;

Drop Table [Add Table]

;

//************************* Identify "Deleted" ************************* //

[Initial Table]:

Load

[Order Number],

     [Product ID],

     [Sales Number],

     [Unit Price],

     [Sales Amount],

     [Order Date]

From [Output\qvd files\IncrementalReload_Initial_P2.QVD](qvd)

;

[Delete Table]:

Left Join LOAD

[Order Number],

'D' as [Flag]

FROM [Data\Incremental Reload.xlsx] (ooxml, embedded labels, table is 工作表2)

;

[Final_Delete Table]:

Load

[Order Number],

     [Product ID],

     [Sales Number],

     [Unit Price],

     [Sales Amount],

     [Order Date],

if([Flag] = 'D', '', 'Delete') as [Modified Type]

Resident [Initial Table]

Where [Flag] <> 'D'

;

Drop Table [Initial Table]

;