Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
;
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)
;