# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Contributor III

## calculate class

Hi everyone

I have two tables:

-First one contains information relative to the class:

For example: if the rank of my product’s sales of brand B1 in France <=2, then I will give him a class A;

The setting of class for each country/brand is different, and interval of rank is larger in Germany than France because we have more products to sale in Germany

--Second one is a straight table which contains information about sales.

In this table I should calculate the rank of sales per product/brand/county and his class according to his rank

In this example:

Product P1 of brand B1 in France has a class C because his sale’s rank is 6.

So for calculating class, I can use “if”:

if(country='France' and brand='B1', if(rank>6,'D', if(rank>4, 'C, if(rank>2, 'B','A'))), if(county='France' and brand='B2' ……..)

But I find my solution is very complicate, I want to find easier way to achieve it.

I can not achive it in script because rank is calculated in the straight table

But how?

Thanks

Tags (1)
4 Replies
Highlighted
Honored Contributor

## Re: calculate class

Hi Zhu,

try:

Rank:

FROM

class.xlsx

(ooxml, embedded labels, header is 1 lines, table is class);

Temp:

max(RankValue) as MaxRank;

PurgeChar(FieldValue('Rank',RecNo()),'<>=') as RankValue

AutoGenerate FieldValueCount('Rank');

Let vMaxRank = Peek('MaxRank',0,'Temp');

Class:

Brand,

Class,

Rank,

if(left(Rank,1) = '>', \$(vMaxRank),PurgeChar(Rank,'<>=')) as To

FROM

class.xlsx

(ooxml, embedded labels, header is 1 lines, table is class);

Class1:

country,

Brand,

Class,

To,

if(country=peek(country) and Brand=peek(Brand),Peek(To),0) +1 as  From

Resident Class Order by country, Brand,To ;

Sales:

brand as Brand,

product,

sales,

Rank

FROM

class.xlsx

(ooxml, embedded labels, table is Sales);

Left Join(Class1)

IntervalMatch (Rank, country, Brand) LOAD From, To, country, Brand Resident Class1;

Left Join(Sales)

LOAD Rank, country, Brand, Class Resident Class1;

Drop Tables Class, Class1,Temp, Rank;

Giving this:

country Class sum(sales)
1058
FranceA429
FranceB310
FranceC216
FranceD103

Regards

Andrew

Honored Contributor

## Re: calculate class

Hi Zhu,

I deleted my original post and replaced it with another. This new script does not read the class from the sales table. It takes the class table and creates intervals of rank then uses the IntervalMatch function to form an association between these intervals and the rank given in the sales table. A join operation then adds the class field to the sales table.

Kind regards

Andrew

Contributor III

## Re: calculate class

Hi Andrew.

I think it was not clear in my original request. I have not field rank in y table salesin which I have only sales per product/brand/country, rank is an expression in my straight chart calculated according to my filters.

Xia

Contributor III

## Re: calculate class

Hi Andrew,

Some feedback for you.

As I said that I have not my rank in my file which is indicator calculated in live in my application.

So I used only firt part of your script to get the liste of my range of class with interval function.

And then I use  concat to create two variables: one for class and another one for mapping like this

concat(Chr(39)&Classe&Chr(39),',', Key).

At the end, I create an expression in my straight table

=

Pick(

Match(

County&'|'&Brand&'|'&Rank(.........    ),

\$(filedsmapping))+1,

'D',\$(class),

).