Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
syukyo_zhu
Creator III
Creator III

calculate class

Hi everyone

I need your helps

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

4 Replies
effinty2112
Master
Master

Hi Zhu,

try:

Rank:

LOAD Rank

FROM

class.xlsx

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

Temp:

LOAD

max(RankValue) as MaxRank;

LOAD

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

AutoGenerate FieldValueCount('Rank');

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

Class:

LOAD country,

     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:

Load

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:

LOAD country,

     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

effinty2112
Master
Master

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

syukyo_zhu
Creator III
Creator III
Author

Hi Andrew.

Thanks for your script.

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

syukyo_zhu
Creator III
Creator III
Author

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),

).

Anyway thanks for your advices.

Xia