Trying to Add a Ranking in Script

[prees959]

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 · Accepted solution]

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]

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]

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