topic Rank Function in load script in QlikView

Rank Function in load script

Thu, 12 Jul 2012 16:11:01 GMT

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

Re: Rank Function in load script

swuehl — Thu, 12 Jul 2012 16:24:15 GMT

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

];

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;

Re: Rank Function in load script

Fri, 13 Jul 2012 08:33:35 GMT

Hey Swuehl

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

Any suggestions /

Thanks

Re: Rank Function in load script

swuehl — Fri, 13 Jul 2012 08:44:39 GMT

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

TmpRank:

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

Re: Rank Function in load script

Fri, 13 Jul 2012 09:23:19 GMT

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

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;

Re: Rank Function in load script

swuehl — Fri, 13 Jul 2012 09:29:21 GMT

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

Load * INLINE [

;

Re: Rank Function in load script

Fri, 13 Jul 2012 09:38:13 GMT

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

Re: Rank Function in load script

swuehl — Fri, 13 Jul 2012 09:49:39 GMT

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

B,1

C,1

A,2

B,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;

Re: Rank Function in load script

Fri, 13 Jul 2012 10:12:32 GMT

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

Re: Rank Function in load script

swuehl — Fri, 13 Jul 2012 10:21:28 GMT

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

Re: Rank Function in load script

Fri, 13 Jul 2012 10:23:07 GMT

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

Re: Rank Function in load script

nagaiank — Sat, 14 Jul 2012 16:38:40 GMT

Rank function is available for use in chart expressions only.It is not available in load scripts. In load script, you may use Order By.

Re: Rank Function in load script

swuehl — Sun, 15 Jul 2012 15:11:50 GMT

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.

Re: Rank Function in load script

Mon, 16 Jul 2012 07:52:43 GMT

OMG Swuehl

Sorry I didnt mean to repost ( that must look rude - sorry !!!) Sometimes my I get errors when posting a message onto the forum and then it posts twice ,

Let me have a look at making a dash that explains my issue

Thanks

Re: Rank Function in load script

Mon, 16 Jul 2012 10:52:25 GMT

Hi guys

Thanks for your time on this -- the issue is with my data model

I have rewrite most of the script to accomodate what I require now

Thanks for your posts they will deffo assist when I go to redesign the dash

Re: Rank Function in load script

Mon, 16 Jul 2012 10:54:35 GMT

Hi guys

Thanks for your time on this -- the issue is with my data model

I have rewrite most of the script to accomodate what I require now

Thanks for your posts they will deffo assist when I go to redesign the dash