Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I want o use Where not Exists for my Order and New Order table.
I have loaded Order_Table and I have transformed and added some new field. Then found out i have another New_Order table which vae some new records need to load in as well. So i wrote the script below. But when i reloaded it, it created Syntetci key.
How to avoid that and able to load in both Order and New Order table records?
Orders:
LOAD
EmployeeID &'|' & Year as order_key,
OrderID,
Month(OrderDate) as Month,
OrderDate,
EmployeeID,
CustomerID,
ProductID,
APPLYMAP('Product_Details',ProductID,'ProductID Not Available in Product Details') as Product_Made,
//APPLYMAP('Product_Details',ProductID,null()) as Product_Made,
ShipperID,
Year,
Sales,
Cost,
Quantity
FROM
[Data Source\Order.xlsx]
(ooxml, embedded labels, table is Order);
New_Order:
LOAD OrderID,
OrderDate,
EmployeeID,
CustomerID,
ProductID,
ShipperID,
Year,
Sales,
Cost,
Quantity
FROM
[Data Source\New_Order.xlsx]
(ooxml, embedded labels, table is Order)
where not exists(OrderID);
Rgds
Jim
with concatenate
Orders:
LOAD
EmployeeID &'|' & Year as order_key,
OrderID,
Month(OrderDate) as Month,
OrderDate,
EmployeeID,
CustomerID,
ProductID,
APPLYMAP('Product_Details',ProductID,'ProductID Not Available in Product Details') as Product_Made,
//APPLYMAP('Product_Details',ProductID,null()) as Product_Made,
ShipperID,
Year,
Sales,
Cost,
Quantity
FROM
[Data Source\Order.xlsx]
(ooxml, embedded labels, table is Order);
//New_Order:
concatenate (Orders)
LOAD OrderID,
OrderDate,
EmployeeID,
CustomerID,
ProductID,
ShipperID,
Year,
Sales,
Cost,
Quantity
FROM
[Data Source\New_Order.xlsx]
(ooxml, embedded labels, table is Order)
where not exists(OrderID);
maybe you have to rename some fields in the second load statement to match the fields in the first load
Hi Maxgro,
Y cant we use Join instead of using concatenate?
Rgds
Jim
I think concat is better in your case as you want to add new records to the same table (new orders to order)
You can find an excellent description of the difference here
I agree with concatenate here too.
Plus, "maybe you have to rename some fields in the second load statement to match the fields in the first load"...
...I think the second table will need the key field "EmployeeID &'|' & Year as order_key", unless you were already referring to this.
hth,
john
So... here's what i have did. But I am not sure . is this correct?
Orders:
LOAD
EmployeeID &'|' & Year as order_key,
OrderID,
Month(OrderDate) as Month,
OrderDate,
EmployeeID,
CustomerID,
ProductID,
APPLYMAP('Product_Details',ProductID,'ProductID Not Available in Product Details') as Product_Made,
//APPLYMAP('Product_Details',ProductID,null()) as Product_Made,
ShipperID,
Year,
Sales,
Cost,
Quantity
FROM
[Data Source\Order.xlsx]
(ooxml, embedded labels, table is Order);
//New_Order:
Concatenate(Orders)
LOAD
EmployeeID &'|' & Year as order_key,
OrderID,
Month(OrderDate) as Month,
OrderDate,
EmployeeID,
CustomerID,
ProductID,
APPLYMAP('Product_Details',ProductID,'ProductID Not Available in Product Details') as Product_Made,
ShipperID,
Year,
Sales,
Cost,
Quantity
FROM
[Data Source\New_Order.xlsx]
(ooxml, embedded labels, table is Order)
where not exists(OrderID);
It's look the same as the 1st load. and there's no more synthetic key.
If i would need to create 2 table box, 1 is to show old record and another is show new record, how should i write it in expression ?
add a field Order Type and set this field to New Orders or Old Orders
you can filter new and/or old orders with this new field in the user interface