15 Replies Latest reply: Jul 16, 2012 6:54 AM by Anne Duffy RSS

    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

        • Re: Rank Function in load script
          Stefan Wühl

          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;

            • Re: Rank Function in load script

              Hey Swuehl

               

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

               

              Any suggestions /

               

              Thanks

               

              A

              • 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

                [S: Qlikview.xls]

                (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

                [S: Qlikview.xls]

                (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
                    Stefan Wühl

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

                     

                    Load * INLINE [

                    ;

                      • 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

                          • Re: Rank Function in load script
                            Stefan Wühl

                            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;

                              • 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

                                 

                                 

                                 

                                 

                                 

                                • 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

                                   

                                   

                                   

                                   

                                   

                                    • Re: Rank Function in load script
                                      Stefan Wühl

                                      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.