topic Re: calculate class in QlikView

calculate class

syukyo_zhu — Tue, 29 Aug 2017 14:05:34 GMT

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

Re: calculate class

effinty2112 — Tue, 29 Aug 2017 15:44:58 GMT

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

Re: calculate class

effinty2112 — Tue, 29 Aug 2017 18:11:56 GMT

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

Re: calculate class

syukyo_zhu — Wed, 30 Aug 2017 07:43:48 GMT

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

Re: calculate class

syukyo_zhu — Mon, 04 Sep 2017 09:18:12 GMT

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