Create an Expression

[#1]

Hey guys

I need to create an expression called Sales by using the following fields:

Quantity

Price

Discount

I already know i need to take Quantity and multiply it by the price and minus the discount. I can do that manually in expressions but i need to create a set expression and it may not be done by using a variable. Can anyone help me create it in the script or something?

Please, any ideas are welcome

[sparur · Accepted solution]

Hello,

why you can't do it in load script? for example such as:

LOAD *,

Quantity * Price * (1-Discount) AS Sales

FROM...

[#3]

Yea i figured it out...

LOAD OrderQty,

ProductID,

UnitPrice,

UnitPriceDiscount,

num((OrderQty*UnitPrice)-UnitPriceDiscount) as Sales;

SQL SELECT OrderQty,

ProductID,

UnitPrice,

UnitPriceDiscount

FROM SalesOrderDetail;

[#4]

When i try to do the same to work out cost, the script keeps loading lines that are way more than there are already... i used

LOAD StandardCost,

OrderQty,

num(StandardCost*OrderQty) as Cost;

SQL SELECT OrderQty,

StandardCost

FROM SalesOrderDetail,

Product;

for cost but it loads more than 15million lines and before i put in the formula it only loads 30 000 lines...?

Any ideas?

[llauses243]

Hi,

Add ProductID in Load & SQL Select sentence, this explain diff in quantity records

/Luis

[#6]

No comprenday

Sorry...

[richnorris]

He's saying, you'll need to load in the ProductID in the table where you calculate cost, ie:

LOAD

ProductID,

StandardCost,

OrderQty,

num(StandardCost*OrderQty) as Cost;

SQL SELECT OrderQty,

ProductID,

StandardCost

FROM SalesOrderDetail,

Product;

At the moment, you are working out 'Sales' for every product, and then for each of these rows, you are working out every 'Cost' for every row. (Cartesian set). How does it know which Cost goes with which Sales amount? It doesn't, so it puts all of them with all of them. (Hence so many rows!) By adding the ProductID to both, it knows which Cost goes with which Sales and puts them on the same row.

Alternatively, you could work out the Sales in the same load statement, which would achieve the same result.

[#8]

Hey guys.

Once again thanks so much for all the responses i really appreciate it. I tried the following:

LOAD

ProductID,

StandardCost,

OrderQty,

num(StandardCost*OrderQty) as Cost;

SQL SELECT OrderQty,

ProductID,

StandardCost

FROM SalesOrderDetail,

Product;

But it came up with this:

Any Ideas?

Thanks alot.

[#9]

no update?

[Miguel_Angel_Baeyens]

Hello,

It's just an SQL writing issue, as ProductID field is present in both Product and SalesOrderDetail tables in your database, and you must specify in your SQL statement which of both you want to get

LOAD ProductID, StandardCost, OrderQty, num(StandardCost*OrderQty) as Cost;SQL SELECT OrderQty, Product.ProductID, StandardCostFROM SalesOrderDetail, Product;

Regards.