topic Create an Expression in QlikView

Create an Expression

Wed, 04 Aug 2010 09:20:29 GMT

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

Create an Expression

sparur — Wed, 04 Aug 2010 10:27:29 GMT

Hello,

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

LOAD *,

Quantity * Price * (1-Discount) AS Sales

FROM...

Create an Expression

Wed, 04 Aug 2010 10:57:49 GMT

Yea i figured it out...

LOAD OrderQty,

ProductID,

UnitPrice,

UnitPriceDiscount,

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

SQL SELECT OrderQty,

ProductID,

UnitPrice,

UnitPriceDiscount

FROM SalesOrderDetail;

Create an Expression

Wed, 04 Aug 2010 11:01:35 GMT

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?

Create an Expression

llauses243 — Wed, 04 Aug 2010 12:12:27 GMT

Hi,

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

/Luis

Create an Expression

Wed, 04 Aug 2010 14:37:47 GMT

No comprenday

Sorry...

Create an Expression

richnorris — Wed, 04 Aug 2010 15:03:21 GMT

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.

Create an Expression

Thu, 05 Aug 2010 07:31:56 GMT

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.

Create an Expression

Thu, 05 Aug 2010 08:58:38 GMT

no update?

Create an Expression

Miguel_Angel_Baeyens — Thu, 05 Aug 2010 09:18:06 GMT

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.

Create an Expression

Thu, 05 Aug 2010 09:42:32 GMT

im sorry but i dnt understand???

Create an Expression

Miguel_Angel_Baeyens — Thu, 05 Aug 2010 09:50:20 GMT

Blaze,

Your "ProductID" field exists in the two tables you are loading, "SalesOrderDetail" and "Product". The error ODBC is throwing and you posted above means that when you do the SQL query, and one fieldname exists in more than one table, you must specify the name of the table from where you want to get that field.

Take into account that the ODBC passes the same statement you have written, so if it has errors, the query won't be executed. I guess that you will get the same error if you perform the same query in your SQL console.

Hope that helps.