calculate class

[syukyo_zhu]

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

[effinty2112]

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
France	A	429
France	B	310
France	C	216
France	D	103

Regards

Andrew

[effinty2112]

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]

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]

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