How To add a column in Data Model Viewer

[11deanm]

So I would like to add a column through Data  Model Viewer where its called Commision where I take the 
total_payment - (soldunitprice*quantity) is this something I am able to do? There are two different tables

[Andrei_Cusnir · Accepted solution]

Hello,

Perhaps you can do something like this:

1. Load the fist dataset to table "Temp"
2. Load the second dataset and left join to table "Temp"
3. Create a new table "Orders" from the dataset that is found in table "Temp"
4. Create the new field, combining the fields from both loaded datasets
5. Drop the "Temp" table

Here are the load scripts that I have used:

//Loading dataset to Temp
Temp:
LOAD
    OrderID,
    CustomerID,
    EmployeeID,
    OrderDate,
    TotalPayment,
    Ostatus
FROM [...]
(...);

//Left Join the other dataset to Temp
Left Join Temp:
LOAD
    OrderID,
    ProductID,
    SoldUnitPrice,
    Quantity
FROM [...]
(...);

//Create table Orders
Orders:
Load *,
    TotalPayment - SoldUnitPrice * Quantity as Commision
Resident Temp;

//Drop Temp table
Drop Table Temp;

The loaded dataset looks, in Data model viewer, like:

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members.