Load Data from 2 Tables and Delta Data

Oguzhan — Mon, 09 Aug 2021 09:14:33 GMT

Hello,

I have following problem. I have a excel file with 155 coloumns filled with data like below.

Table A:

Order_request	Order Number	Project ID
135305	561225	A
135306	561231	A
135307	561233	A
135343	561235	A
140610	582309	A
141107	583893	A
142373	587627	A
142374	587629	A

Then I have another Excel File with the same 155 coloumns filled with data like below

Table B:

Order_request	Order Number	Project ID
132452	551550	A
135305	561225	A
135306	561231	A
135307	561233	A
135343	561235	A
134779	562952	A
138725	573193	A
132529	580485	A
140610	582309	A
141107	583893	A
141418	585626	A
142373	587627	A
142374	587629	A
142725	588691	A
142938	589539	A
142938	589540	A
143778	592252	A
143979	592254	A
144231	593478	A
143919	594151	A
144684	594817	A

Table A is my main table that contains a lot more Data than shown here so I cannot only load Table B,

What I need to load is all date in one table but the duplicate one like the red highlighted Data because it appears in both tables. So I basically want to add the delta to my data without duplicating the whole dataset because some of the Order numbers can also be used in another Project B and if this happens I need the table be like.

The green Highlighted Data comes from another Project

Order_request	Order Number	Project ID
132452	551550	A
135305	561225	A
132452	551550	B

Thank you in Advance for your Help

Best regards

Oguzhan

Re: Load Data from 2 Tables and Delta Data

stevejoyce — Mon, 09 Aug 2021 10:19:57 GMT

You can use Where Not Exists in your table B load. You need a key or composite key to evaluate if it's been loaded already. If your composite key (call it %TableKey for example) is Order_request, Order_Number, Product_ID, you can do this... first load table A, create composite key, when loading B use same composite logic as your exsists expression and compare to the %tablekey for what has (not) been loaded already:

TableA:

Load *

,Order_request &'|' & Order_Number &'|' & Product_ID as %TableKey

From <your from statement A>

;

//table b load

Concatenate(TableA)

Load *

,Order_request &'|' & Order_Number &'|' & Product_ID as %TableKey

From <your from statement B>

Where Not Exists (Order_request &'|' & Order_Number &'|' & Product_ID, %TableKey)

;

topic Re: Load Data from 2 Tables and Delta Data in App Development

Load Data from 2 Tables and Delta Data

Re: Load Data from 2 Tables and Delta Data