Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Rank Function in load script

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Rank Function in load script

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.

15 Replies
MVP
MVP

Re: Rank Function in load script

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;

Not applicable

Re: Rank Function in load script

Hey Swuehl

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

Any suggestions /

Thanks

A

MVP
MVP

Re: Rank Function in load script

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

TmpRank:

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

Not applicable

Re: Rank Function in load script

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;

MVP
MVP

Re: Rank Function in load script

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

Load * INLINE [

;

Not applicable

Re: Rank Function in load script

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

MVP
MVP

Re: Rank Function in load script

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;

Not applicable

Re: Rank Function in load script

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

ReasonSubsNumber of RejectionsRankCorrect  Rank
Incorrect Docket36321
Fire Damage15052
Under use14243
Bad Rate12334
Trade Green11875
Knife Sharp11266

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

 

MVP
MVP

Re: Rank Function in load script

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

Community Browser