Calculation in the Load Script

[#1]

Hello,

I load sales data by Transaction from one file,

Transaction No.

Product Name

Barcode

Product Category

Qty

Sales

GM

etc etc...

But some products are measured by tonnage so I need to multiply the Qty by a field called Multiplier which I load from a different file.

I currently use an expression to do this, Qty * Multiplier

This works in my Straight Table when Barcode is the dimension

But if I use category as the dimension the expression returns an error.

so I am wondering if I can do a calculation in the load script to make a new field called Calculated Tonnage which would be Qty * Multiplier (Qty from the Sales Table, and Multiplier from the Multiplier Table).

Any suggestions would be greatly appreciated,

Thanks,

Ben.

[#2]

Hello Ben,

If I understand what you want, you can do a left join between two tables and put the field multiplication as a new field in the sales table. Read this query again as Resident and create a new field by multiplying the desired field by field multiplication.

Hope this helps!

God Bless You!

Stive

Brasil

[#3 · Accepted solution]

Hi Ben,

What you can do is a left join to the table containing the multiplier or a mapping load, in order to get the multiplier into your transactions table for the calculation.

As i understood, the barcode is a key field so you can do like below:

Example1:

Map_Multiplier:

//this table will be automatically dropped after script execution as the load is preceded by mapping

Mapping Load Distinct Barcode, Multiplier

From multiplier_table;

Transactions:

Load Barcode,

Qty,

Qty * ApplyMap('Map_Multiplier', Barcode) as CalculatedTonnage

from transactions_table;

Example2:

tempTransactions:

Load Barcode,

Qty

From transactions_table;

left join

Load Distinct Barcode,

Multiplier

From multiplier_table;

Transactions:

Load *,

Qty * Multiplier as CalculatedTonnage

Resident tempTransactions;

drop table tempTransactions;

Hope this helps you!

[#4]

Hi,

Tried example 2, ran into a problem though.....

I get my information from several different files (2010 Q1, 2010 Q2, 2010 Q3, 2010 Q4, 2011 Q1, 2012 Q1 etc), in the table viewer it puts all the Sales data into a table called 2010 Q1, but when I put in From [2010 Q1]; it just says can't find the table in the folder where I have the Qlikview file saved.

Any help would be great.

Thanks,

Ben.

[#5]

Sorted it!

needed to use RESIDENT and not FROM....

Thanks

Ben.