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

Aggregating from a previous field within a load script

Here's the first few lines of the table I am pulling my data from:

ProductTotal LicensesCustomer Number

Basic

12379
Complete304568
Basic1238524
Plus75557
Plus444535
Basic17896
Complete17565
Basic88966

I would like to calculate what percent of total licenses are comprised of each product.  For example, if I have 3,000 licenses for the product "Basic" and 10,000 licenses for all three products, then Basic would be 30% of consumer products.  I need to use that 30% in other calculations.

I'm using the following to make a chart in my dashboard for expression "Percent Consumer".  It works.

sum({<Product = {'Basic'}>/<Product = {'Plus'}>/<Product = {'Complete'}>}[Total Licenses])/

(sum(total {<Product = {'Basic'}>}[Total Licenses])+

sum(total {<Product = {'Plus'}>}[Total Licenses])+

sum(total {<Product = {'Complete'}>}[Total Licenses]))

What I'd really like to do is do the same kind of thing in my load script so I can use this value on other data I'm loading.  I am trying the following:

if(Product='Basic',

              lookup([Total Licenses],Product,'Basic','License_By_Product')/

              (lookup([Total Licenses],Product,'Basic','License_By_Product')+

              lookup([Total Licenses],Product,'Plus','License_By_Product')+

              lookup([Total Licenses],Product,'Complete','License_By_Product')),

              if(Product='Plus',

                  lookup([Total Licenses],Product,'Plus','License_By_Product')/

                  (lookup([Total Licenses],Product,'Basic','License_By_Product')+

                  lookup([Total Licenses],Product,'Plus','License_By_Product')+

                  lookup([Total Licenses],Product,'Complete','License_By_Product')),

                  if(Product='Complete',

                      lookup([Total Licenses],Product,'Complete','License_By_Product')/

                      (lookup([Total Licenses],Product,'Basic','License_By_Product')+

                      lookup([Total Licenses],Product,'Plus','License_By_Product')+

                      lookup([Total Licenses],Product,'Complete','License_By_Product'))

                      ,0))) as [Percent Consumer]


It's outputting a number, but it's the wrong number.  I believe the reason for this is that "lookup" is only looking at one line in the table above.  What I need is a way to sum up all the lookup values for a product.  I tried using "sum" or "aggr" in conjunction with "lookup", but either that doesn't work or I'm doing it wrong.


Any advice?

1 Solution

Accepted Solutions
nagaiank
Valued Contributor III

Re: Re: Re: Aggregating from a previous field within a load script

Updated application with some changes in load script and a chart with simpler expressions is attached.

Is this what you were looking for?

7 Replies
nagaiank
Valued Contributor III

Re: Aggregating from a previous field within a load script

Create a Straight table

Dimension: Product

Expression 1: Sum([Total Licenses])

Expression 2: Sum([Total Licenses])/Sum(TOTAL [Total Licenses])

Not applicable

Re: Aggregating from a previous field within a load script

The straight table isn't the issue.  I have been able to do that with no issues.

The reason I'm not suing Sum(TOTAL expression) above is that there are some Product values I don't want to include that aren't included in my table example above.

I need to get this value in my load script so I can use the percent when I load other data. How might I do that?

nagaiank
Valued Contributor III

Re: Aggregating from a previous field within a load script

One option is to use a flag with values 1 or 0 for each product (if it is based on the product) depending on if you want to include or exclude a product and use

Sum(TOTAL (Flag *[Total Licenses]))

for getting the total.


If you give your complete requirements, you may get more useful answer from the forum.

Not applicable

Re: Re: Aggregating from a previous field within a load script

I am attaching an example of what I'm trying to describe.  Maybe this will help explain what I'm looking for.

nagaiank
Valued Contributor III

Re: Re: Re: Aggregating from a previous field within a load script

Updated application with some changes in load script and a chart with simpler expressions is attached.

Is this what you were looking for?

MVP
MVP

Re: Aggregating from a previous field within a load script

flag the product in the script

License_By_Product:

LOAD [Product],

     [Total Licenses],

     [Customer Number],

     if(Match(Product, 'Basic', 'Plus', 'Complete'), 1, 0) as Flag

FROM

Licenses.xlsx

(ooxml, embedded labels, table is Sheet1);

then use in your chart

expression     =sum({$ <Flag={1}>} [Total Licenses])

1.png

Not applicable

Re: Re: Re: Aggregating from a previous field within a load script

Thanks!!! 

Community Browser