round() with groupby help

[mahitham]

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.

[sasiparupudi1]

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

[petter]

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;