2 Replies Latest reply: Apr 18, 2018 4:00 AM by Petter Skjolden RSS

    round() with groupby help

    Mahitha M

      Hi Experts,

       

      Can any one please help me to derive the Percentageofprofitloss calculated field in the below query into qlik

       

      In the oracle query this field is derived like below.

       

       

       

       






        • Re: round() with groupby help
          Sasidhar Parupudi

          May be try using applymap

           

          MAP_profitlossofdepartment:

          Mapping Load

          date &'|'&departmentid  ,

          sum(profitlossofdepartment)

          resident reportdata

          group by 

          departmentid,

          date ;

           

           

          Reportdatard:

          Load

          departmentname                        as    rd.departmentname,

          departmentid                              as    rd.departmentid,

          date                                            as    rd.date,

          departmentdescription                as    rd.departmentdescription,              

          profitlossofdepartment                as    rd.profitlossofdepartment ,            

          currency                                    as    rd.currency,

          factoryname                              as    rd.factoryname,

          subdepartment                            as  rd.subdepartment,

          Round((profitlossofdepartment/(ApplyMap('MAP_profitlossofdepartment',date &'|'&departmentid,0)*100),.01) as Percentageofprofitloss

          resident reportdata

           

           

          order by

          rd.departmentname,

          rd.currency,

          rd.factoryname

          ;

          hth

          • Re: round() with groupby help
            Petter Skjolden

            You can often (if not always) rewrite a sub select in a SQL database to a join instead.

             

            So you are on the right track. You will have to do a Qlik join - following Qlik's conventions and syntax when joining tables. First of all you don't have the ON clause in Qlik since joins will be done as natural joins using the name of the fields (columns in SQL) to join the two tables so you should not make date and departmentid into table specific names just keep them as is because that will tell Qlik the same as the ON clause does in Oracle.

             

            It could probably look like this:

             

            Reportdatard:

            Load

            rd.departmentname, rd.date, departmentid as rd.departmentid, .....all the other fields.....

            order by rd.departmentname, rd.currency, rd.factoryname

            ;

            Load

              departmentname          as    rd.departmentname,

              date                    as    rd.date,

              departmentid,

              departmentdescription   as    rd.departmentdescription,            

              currency                as    rd.currency,

              factoryname             as    rd.factoryname,

              subdepartment           as    rd.subdepartment

            resident

              reportdata

            order by

              rd.departmentname, rd.currency, rd.factoryname


            Left Join

             

            Load

              departmentid,

              Round(sum(profitlossofdepartment)*100,'0.01') as ri.profitlossdepartment

            resident

              reportdata

            group by 

              departmentid;