topic Multiply (or divide) two values from the same dimension in load script in QlikView

Multiply (or divide) two values from the same dimension in load script

Thu, 23 Aug 2012 09:41:27 GMT

I want to create calculated value based on other values from the same dimension. As an example, I have an account Sales and an account COGS. Now I want to create a new account "COGS in % of Sales". This would be easy in a table but I need to create this in the load script.

LoadData:

LOAD * INLINE [

Period, Account, Value

201201, Sales, 100

201201, COGS, 50

201202, Sales, 110

201202, COGS, 45

];

Now I would need something like

concatenate

load Period,

"COGS in % of Sales" as Account,

(load Value resident LoadData where Account = 'COGS') / (load Value resident LoadData where Account = 'Sales') as Value

Resident LoadData;

Any idea?

Re: Multiply (or divide) two values from the same dimension in load script

jonathandienst — Thu, 23 Aug 2012 09:46:24 GMT

Try this

concatenate

load Period,

"COGS in % of Sales" as Account,

Sum(If(Account = 'COGS', Value)) / Sum(If(Account = 'Sales', Value)) as Value

Resident LoadData

Group By Period;

Regards

Jonathan

Re: Multiply (or divide) two values from the same dimension in load script

Anonymous — Thu, 23 Aug 2012 10:25:36 GMT

The 'COGS in % of Sales' must be in single quotes.

Regards,

Michael

Re: Multiply (or divide) two values from the same dimension in load script

jonathandienst — Thu, 23 Aug 2012 13:57:01 GMT

Oops