4 Replies Latest reply: Apr 15, 2018 9:44 PM by Sasidhar Parupudi RSS

    Group by help

    P Kumar

      Hi Experts,

       

      Can any one please help me on below requirement.

      I have a oracle query with Group by, Having and Order by clause like below.

       

       

      In Qlik we are working with QVD's to build the model by taking the logic from above oracle query.

      Up to Group by able to run in Qlik by replicating all logics into Qlik but struck after having clause.

       

      Please help me how to replicate the logic into Qlik from having to order by.

       

      Thanks in advance.

        • Re: Group by help
          Petter Skjolden

          You would have to have a preceding LOAD statement and this LOAD could have a WHERE acting just like a HAVING clause that refers to a field that has a new name and could be an aggregate like max().

           

          LOAD
            *
          WHERE
            Not(IsNull( [Type of limit] )); // this will refer to the newly calculated max(....)
          LOAD
            .....
            max([Type of limit]) as [Type of limit],
            .....
          order by Desk_Name,
            Desk_ID,
            Desk_Description,
            .....
          ;
          
          
          




          Here is a sample fully working test-script:


          DATA:
          LOAD
            Chr( Floor(Rand()*16)+Ord('A') ) AS C1,
            Chr( Floor(Rand()*26)+Ord('A') ) AS C2,
            If(Rand()<0.01,Null(),Ceil( Rand()*1000) + 1000) AS N
          AUTOGENERATE
            10000;
          
          
          LOAD
            C1,
            MinC2,
            SumN
          WHERE Not(IsNull(SumN))   // This will work as the HAVING clause
            ;
          LOAD
            C1,
            MinString(C2) AS MinC2,
            If(Rand()<0.1,Null(),Sum(N)) AS SumN    // Let approximately 10% be NULLS
          RESIDENT
            DATA
          GROUP BY
            C1
          ORDER BY
            C1 DESC 
          ;
          
          
          

           

           


            • Re: Group by help
              Petter Skjolden

              You have to look at your data in the table when you comment out the red. Does the column [Type of limit] really contain any non-null values?

              • Re: Group by help
                Keerthika B

                Hi,

                     Does your METRICNAME field has more than 15 characters in length

                mid(METRICNAME,15)  this may cause the issue if length is less than 15.


                keerthika

                • Re: Group by help
                  Sasidhar Parupudi

                  May be try a resident load

                   

                  Groupby:

                  Load

                        DESKNAME,

                        DATASECURITYID, 

                        DESKDESCRIPTION,

                        "Unit of Measure",

                        Desk_Currency_Reported,

                        groupingMetric,

                        isStopLoss,

                      ASOFDATE ,                                        

                        max([Type of limit])                                      as [Type of limit],

                        max([Limit Size])/1000                                    as [Limit Size],

                        round(sum([Value of Usage])/1000, 2)                      as [Value of Usage],

                        Round(fabs(if(isStopLoss = 'Y', RangeMin(Sum([Limit Usage]),0),Sum([Limit Usage]))),0.01) as [Limit Usage]

                  Resident MainTable_R1

                        Group by DESKNAME,

                        DATASECURITYID,

                        DESKDESCRIPTION,

                        "Unit of Measure",

                        Desk_Currency_Reported,

                        groupingMetric,

                        isStopLoss,

                      ASOFDATE

                    order by DESKNAME,

                        DATASECURITYID,

                        DESKDESCRIPTION,

                        Desk_Currency_Reported,

                        [Type of limit]

                      ;

                    Drop table MainTable;

                   

                  Final:

                  NoConcatenate Load

                   

                  *

                  Resident Groupby

                  When Len(Trim([Type of limit])>0

                  ;

                  Drop Table Groupby;