Rank Function in load script

[#1]

Hi guys

I am looking to Rank Rejection reasons as per the frequency they occur.

I have the Rejection reasons loading from an excel spreadsheet , where the rejection reasons are all listed in one cell - so I have split out each reason on load using the below.

if(trim(upper(subfield([Rejection Reasons], ',')))='',null(),trim(upper(subfield([Rejection Reasons], ',')))) as ReasonSub

I now would like to rank it in the script ,as I do not want the rankings to change when certain filters are selected in the chart .

Can anyone advise if this can be done and if so any pointers greatly appreciated

Thanks

A

[swuehl]

If you need to do it in the script, you can try it like this (assuming you want a rank by count of reasons):

Details:

LOAD * INLINE [

ReasonSub

A

A

B

C

C

C

];

TmpRank:

LOAD ReasonSub, count(ReasonSub) as Count, rank(count(ReasonSub)) as Rank2 resident Details group by ReasonSub;

Rank:

LOAD *, recno() as Rank Resident TmpRank order by Count desc;

drop Table TmpRank;

[#3]

Hey Swuehl

Im getting an error stating that Ran is not a valid function !!

Any suggestions /

Thanks

A

[swuehl]

Sorry, posted the wrong snippet, just remove the Rank2 calculation:

TmpRank:

LOAD ReasonSub, count(ReasonSub) as Count  resident Details group by ReasonSub;

[#5]

Hi Swuehl,

Its not coming back with an error but the answers are incorrect. I'm not really sure where its getting it from

This a part of

is my script  , can you see where I may have gone wrong ?

Thanks so much

A

Input:

Load *,

applyMap('RejectMap',ReasonSubs,null()) as ADFCats;

Load

upper([Customer Name]) as [ADF Customer Name],

Len(keepchar([Rejection Reasons],',')) as NoOfReasons,

if(trim(upper(subfield([Rejection Reasons], ',')))='',null(),trim(upper(subfield([Rejection Reasons], ',')))) as ReasonSubs

FROM

(biff, embedded labels, header is 3 lines, table is ADF$);

AllADF:

Load    

recno() as RecID,

[Time received],

[Time approved / declined],

[Date commenced],

[Time commenced],

[Date received by ADF],

Weekday( [Date received by ADF]) as ADFDay,

Date([Date received by ADF]) as ADFDate,

Month(Date([Date received by ADF])) as ADFMonth,

Week(Date([Date received by ADF])) as ADFWeek,

Year(Date([Date received by ADF])) as ADFYear,

WEEKEND([Date received by ADF]) AS WEEKENDING,

[Date closed],

upper([Customer Name]) as [ADF Customer Name],

upper(Branch) as [ADF Branch],

[Parent] as [ADF Parent NSC],

[Account Number],

[Parent]&num([Account Number],'00000000')&Value as ACCOUNTNUMBER,

[Sector Code] as [ADF Sector Code],

[Approved? N/Y],

if([Approved? N/Y]='Yes','Approved','Declined') as [Approved/Declined],

if(left([Rejection Reasons],1)=',',[Rejection Reasons],','&[Rejection Reasons]) as [Rejection Reasons],

'ADF' as Activity,

if(index([Rejection Reasons], ',', -1 )='0','No','Yes') as Unique,

if(isnull([Date closed]),interval( time(now(),'hh:mm:ss.fff') - ([Date received by ADF]+[Time received]) )) as OpenTime,

ReceivedTM,

CommenceTM,

ClosedTM,

[Turnaround Time]

FROM

(biff, embedded labels, header is 3 lines, table is ADF$);

Load * INLINE [

;

TmpRank:

LOAD ReasonSubs, count(ReasonSubs) as ReasonSubCount  resident Input group by ReasonSubs;

Rank:

LOAD *, recno() as Rank Resident TmpRank order by ReasonSubCount desc;

drop Table TmpRank;

[swuehl]

I think this should not be part of your script, try removing

Load * INLINE [

;

[#7]

Hi Swuehl,

Sorry that was just a pasting issue in the actual script its

Load * INLINE [

reportID,

Volume MIS]

;

I use it to show/Hide certain charts

Any other idea what it might be !

Thanks again for your help

A

[swuehl]

Ok,

what do you mean with 'the answers are incorrect'? It's always more than helpful to tell us what you get in detail and what you expect.

I can see one possible issue since you are loading a customer name. So I assume that you need to calculate your rank per customer.

In my example, it could look like:

Details:

LOAD * INLINE [

ReasonSub, Customer

A,1

A,1

B,1

C,1

C,1

C,1

A,2

B,2

B,2

B,2

C,2

C,2

];

TmpRank:

LOAD Customer, ReasonSub, count(ReasonSub) as Count resident Details group by Customer, ReasonSub;

Rank:

LOAD *, autonumber(recno(),Customer) as Rank Resident TmpRank order by Customer, Count desc;

drop Table TmpRank, Details;

[#9]

Using the script as I pasted it in , this is the chart

ReasonSubs	Number of Rejections	Rank	Correct  Rank
Incorrect Docket	363	2	1
Fire Damage	150	5	2
Under use	142	4	3
Bad Rate	123	3	4
Trade Green	118	7	5
Knife Sharp	112	6	6

So Rank is the result and Correct Rank is the desired result

To be honest Im not too concerned about ranking at customer level , I just want to see the most popular rejection reasons

In the chart I used expression

rank(sum({$<[Approved? N/Y] = {"*No*"}>}Numbers),ReasonSubs,1)

And this worked fine - but I really would like to show the ranking whilst not effected by filters

Thanks

[swuehl]

I think I don't really get what you are after. Please provide a small sample app together with your expected result.