Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Highlighted
vidyasagar159
Contributor II

Create Ranks in Script Editor - QlikSense

Hi All,

Hope you guys are doing great.

I would like to create a Ranks for the below sample data in QlikSense using Script Editor. I tried RowNo() but unsuccessful. Can some please help me with the logic?

Thanks in Advance!!!

Product CategoryProduct NameAmountRanks for Products In Each CategoryRanks for Categories
AA1$10011
AA2$20021
AA3$30031
AA4$40041
AA5$50051
BB1$10012
BB2$20022
BB3$30032
BB4$40042
BB5$50052
BB6$60062
Labels (3)
1 Solution

Accepted Solutions

Re: Create Ranks in Script Editor - QlikSense

You can give this a shot

Table:
LOAD * INLINE [
    Product Category, Product Name, Amount
    A, A1, $100
    A, A2, $200
    A, A3, $300
    A, A4, $400
    A, A5, $500
    B, B1, $100
    B, B2, $200
    B, B3, $300
    B, B4, $400
    B, B5, $500
    B, B6, $600
];

FinalTable:
LOAD *,
	 If([Product Category] = Previous([Product Category]), RangeSum(Peek('Ranks for Products In Each Category'), 1), 1) as [Ranks for Products In Each Category],
	 If([Product Category] = Previous([Product Category]), Peek('Ranks for Categories'), RangeSum(Peek('Ranks for Categories'), 1)) as [Ranks for Categories]
Resident Table
Order By [Product Category], [Product Name];

DROP Table Table;
2 Replies

Re: Create Ranks in Script Editor - QlikSense

How are you creating the Ranks for Categories here? Is this based on Sum(Amount)? The lower the Sum(Amount), the lower the rank?

Re: Create Ranks in Script Editor - QlikSense

You can give this a shot

Table:
LOAD * INLINE [
    Product Category, Product Name, Amount
    A, A1, $100
    A, A2, $200
    A, A3, $300
    A, A4, $400
    A, A5, $500
    B, B1, $100
    B, B2, $200
    B, B3, $300
    B, B4, $400
    B, B5, $500
    B, B6, $600
];

FinalTable:
LOAD *,
	 If([Product Category] = Previous([Product Category]), RangeSum(Peek('Ranks for Products In Each Category'), 1), 1) as [Ranks for Products In Each Category],
	 If([Product Category] = Previous([Product Category]), Peek('Ranks for Categories'), RangeSum(Peek('Ranks for Categories'), 1)) as [Ranks for Categories]
Resident Table
Order By [Product Category], [Product Name];

DROP Table Table;