Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I am slow from time to time, but I am not seeing how just repeating your message helps me here.
In your expression
rank(sum({$<[Approved? N/Y] = {"*No*"}>}Numbers),ReasonSubs,1)
how do you derive Numbers? Can't see it in your script.
What does the ReasonSubsCount show when compared to your Number of Rejections?
If I understand your request correctly, I still think that calculating the rank in the script should be possible using a count (or whatever appropriate) in a group by load and the assign a rank number in a order by load.
If it's all about creating a rank expression in the front end that is not sensitive to selections, you can try using set analysis (set identifier 1).
See attached.
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;
Hey Swuehl
Im getting an error stating that Ran is not a valid function !!
Any suggestions /
Thanks
A
Sorry, posted the wrong snippet, just remove the Rank2 calculation:
TmpRank:
LOAD ReasonSub, count(ReasonSub) as Count resident Details group by ReasonSub;
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;
I think this should not be part of your script, try removing
Load * INLINE [
;
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
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;
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
I think I don't really get what you are after. Please provide a small sample app together with your expected result.