topic Re: Trying to Add a Ranking in Script in QlikView

Trying to Add a Ranking in Script

prees959 — Thu, 02 Nov 2017 23:01:43 GMT

Hi, I am using the following script to order and Rank a temporary table :

Ranked:

load AutoNumber([Area] & ' - ' & [DayOfWeek] & ' - ' & [Staff Type]) as Rank,

[Area] & ' - ' & [DayOfWeek] & ' - ' & [Staff Type] as [Detail],

sum([Total]) as [Total]

Resident Detail_Data

group by [Area],[DayOfWeek] ,[Staff Type];

When I look at this tables contents the autonumber isn't doing exactly what I intended.... the output is similar to this :

But, what I would ideally like to show is the following where the rank is split where the total is the same :

Can this be achieved??

Many thanks,

Phil

sunny_talwar — Thu, 02 Nov 2017 23:15:10 GMT

May be you want this

Ranked:

LOAD AutoNumber(Total) as Rank,

LOAD [Area] & ' - ' & [DayOfWeek] & ' - ' & [Staff Type] as [Detail],

Sum([Total]) as [Total]

Resident Detail_Data

Group By [Area], [DayOfWeek], [Staff Type];

prees959 — Fri, 03 Nov 2017 09:43:36 GMT

Hi Sunny,

Thanks for your reply. Unfortunately, I'm getting an error on the first part of the script

Field Not Found Total

If I comment out the first part the script runs but without the rank field...

Ranked:

//LOAD AutoNumber(Total) as Rank,

// *;

LOAD [Area] & ' - ' & [DayOfWeek] & ' - ' & [Staff Type] as [Detail],

Sum([Total]) as [Total]

Resident Detail_Data

Group By [Area], [DayOfWeek], [Staff Type];

prees959 — Fri, 03 Nov 2017 09:53:14 GMT

Fixed it :

Ranked:

LOAD AutoNumber(sum(Total)) as Rank,

[Area] & ' - ' & [DayOfWeek] & ' - ' & [Staff Type] as [Detail],

Sum([Total]) as [Total]

Resident Detail_Data

Group By [Area], [DayOfWeek], [Staff Type];

Thanks for your help again,

Phil