Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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?

1 Solution

Accepted Solutions
MVP
MVP

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

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
3 Replies
MVP
MVP

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

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mov
Esteemed Contributor III

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

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

Regards,

Michael

MVP
MVP

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

Oops

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser