topic Re: Class in QlikView

Class

eptaqlik — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I would like in a pivot table to insert a class that tells me for each material for how many months it has been sold and group the materials for that class.

I attach excel file with starting data and data as I wish

Thanks for your help.

Marco

Re: Class

jyothish8807 — Fri, 22 Jun 2018 11:40:19 GMT

Hi Marco,

Please refer the sample attached.

Br,

Re: Class

YoussefBelloum — Fri, 22 Jun 2018 11:47:59 GMT

here is another alternative if you can't do it on the script:

Calculated dimension:

=aggr(Count({<CodArticolo-={"=sum(Sales1)=0"}>}Sales1)

+Count({<CodArticolo-={"=sum(Sales2)=0"}>}Sales2)

+Count({<CodArticolo-={"=sum(Sales3)=0"}>}Sales3)

+Count({<CodArticolo-={"=sum(Sales4)=0"}>}Sales4)

+Count({<CodArticolo-={"=sum(Sales5)=0"}>}Sales5)

+Count({<CodArticolo-={"=sum(Sales6)=0"}>}Sales6),CodArticolo)

or this expression:

=Count({<CodArticolo-={"=sum(Sales1)=0"}>}Sales1)

+Count({<CodArticolo-={"=sum(Sales2)=0"}>}Sales2)

+Count({<CodArticolo-={"=sum(Sales3)=0"}>}Sales3)

+Count({<CodArticolo-={"=sum(Sales4)=0"}>}Sales4)

+Count({<CodArticolo-={"=sum(Sales5)=0"}>}Sales5)

+Count({<CodArticolo-={"=sum(Sales6)=0"}>}Sales6)

Re: Class

neha_shirsath — Fri, 22 Jun 2018 11:52:08 GMT

Hi,

Can you elaborate more, as in excel provided on which basis class will be calculated?

Provided data is not sufficient to get output.

-Regards,

Neha

Re: Class

eptaqlik — Fri, 22 Jun 2018 12:23:54 GMT

Hi KC,

I have my data in the "Start" sheet

in the "Result" sheet I calculated the class by counting the period in which there were sales and I ordered the data in descending order of class.

many tks

Marco

Re: Class

eptaqlik — Fri, 22 Jun 2018 12:25:37 GMT

Hi Neha,

I have my data in the "Start" sheet

in the "Result" sheet I calculated the class by counting the period in which there were sales and I ordered the data in descending order of class. Only the column "A" was calculated the others field in column are the same.

many tks

Marco

Re: Class

jyothish8807 — Fri, 22 Jun 2018 12:47:16 GMT

Hi Marco,

The same has been implemented in attached sample above, please have a look

Script:

LOAD * INLINE [

CodArticolo, Sales 1, Sales 2, Sales 3, Sales 4, Sales 5, Sales 6

AA, 0, "5,754.00", 0, "4,790.00", 0, 0

BB, 0, 0, 0, 0, 250.00, 0

CC, 0, 712.00, 0, 0, 0, 0

DD, "5,825.40", "9,475.22", "1,308.10", "1,924.50", "1,240.60", "1,824.60"

EE, "11,715.60", "2,170.20", "4,188.69", 841.64, "9,310.50", "4,360.43"

GG, 980.00, "1,225.00", 244.00, 0, 980.00, 0

HH, "1,801.67", 485.68, 263.00, 255.46, "1,577.48", 0

II, 0, 0, 230.00, 0, 0, 0

LL, "9,503.78", 0, "4,930.45", "1,359.37", 0, "2,719.41"

MM, 526.48, 0, 0, 583.42, "1,886.68", 0

];

CrossTable(Sale_New, Data)

LOAD *

resident A;

NoConcatenate

LOAD

CodArticolo,

if(Data>0,1,0) as Flag

Resident B;

DROP table B;

Re: Class

eptaqlik — Fri, 22 Jun 2018 13:20:53 GMT

Hi KC,

I change some things in your script but without your help I cannot solve the problem.

many tks.

Marco

LOAD * INLINE [

CodArticolo, Sales 1, Sales 2, Sales 3, Sales 4, Sales 5, Sales 6

AA, 0, "5,754.00", 0, "4,790.00", 0, 0

BB, 0, 0, 0, 0, 250.00, 0

CC, 0, 712.00, 0, 0, 0, 0

DD, "5,825.40", "9,475.22", "1,308.10", "1,924.50", "1,240.60", "1,824.60"

EE, "11,715.60", "2,170.20", "4,188.69", 841.64, "9,310.50", "4,360.43"

GG, 980.00, "1,225.00", 244.00, 0, 980.00, 0

HH, "1,801.67", 485.68, 263.00, 255.46, "1,577.48", 0

II, 0, 0, 230.00, 0, 0, 0

LL, "9,503.78", 0, "4,930.45", "1,359.37", 0, "2,719.41"

MM, 526.48, 0, 0, 583.42, "1,886.68", 0

];

CrossTable(Sale_New, Data)

LOAD *

resident A;

NoConcatenate

LOAD

if(Data>0,1,0) as Flag

Resident B;

DROP table A;

DROP table B;

Re: Class

jyothish8807 — Mon, 25 Jun 2018 04:58:11 GMT

Hi Marco,

In Table "C" dont use load *. It will create synthetic key. Keep it like this:

CodArticolo filed will act as the key among both tables. You can join table A and Table C also if you want.

Also dont drop table A.

NoConcatenate

LOAD

CodArticolo,

if(Data>0,1,0) as Flag

Resident B;

DROP table B;

Re: Class

eptaqlik — Mon, 25 Jun 2018 07:48:59 GMT

Hi KC,

many tks.

ciao

Marco