Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MEDHA07
Contributor III
Contributor III

How to do group by specific ids level in front end in qliskense app

Hi All,

I need to show default all SIC id data in table based on range.

I am having range need to show same range in report.

 

I have created the oracle query to pull data from db and getting correct values.

now i am facing issue here is i though they are doing group by backedn script and implemeted same at mysdie.

i am getting data for default all sic ids data correct.Please find below qlik script.

when i am selecting data for individual this is not working because i am doing group by in backedn based on  Locid and polid.

individual SICid is not giving correct data becasue we did not add the SICID in the group by.

we have same locid and polid available in two different SCIids and it has different values the other id has unique key.

hence backend script will not for my case.

i need to do the logic front end only

first i should pass my selections and then it should apply group by at locid andpolid.

in oralce first i am loading all data and doing group by that level.

i am giving where condition first then applying group by post my filter values.

this way it is giving correct group by values even it has individual SICID data and if both scidids has same locid and pol id .

9111  has 592&123    value -1045000 individaul should come less the 5 Million

9121 has 592&123   value -20000000 individual should come <= 20M

when we do group by post selection these two sics id it should group locid and polid and 

sum value 30450000- it should come in range 30m-35 m

now i am facing issue when i do individual i am getting both sicid selections 30M value in 30 m range.

beacause in qlik script ia lready did group in resident table.please find below qlik script.

could you please help m e how to do in fornt end table.

 

 

oracle logic:

Select Count(1) as "Pol Loc",
Sum("TOTAL 100%") as "TOTAL 100% ALL",
sum(pREm) as "Prem all"

From
(
Select
polID ,
LocID,
sysrcna,
sicid,
GRP_AB ,
case when X_I = 'Y' then 'No' else 'Yes' end as "Exclude ",
case when GRP_C = '001' then 'D'
when GRP_C = '002' then 'I' else '' end as "Type",

Sum(NVL(VALUE1,0)) as "TOTAL 100%",
sum(NVL(VALUE2,0)) as pREM
From

JOINS

AND GRP_C IN ('001','002')
WHERE BU_C = 1
AND REGN_C = 'AZ'
AND CNTRY_C = 'AA' and sicid in ('9111','9121','9131','9199')
Group By
polID ,
LocID,
sysrcna,
sicid,
GRP_AB
GRP_AB ,
case when X_I = 'Y' then 'No' else 'Yes' end ,
case when cvty.CV_TY_GRP_C = '001' then 'D'
when GRP_C = '002' then 'I' else '' end

)A
group by pol_Dim_ID ,
Loc_ID
--having Sum(NVL("TOTAL 100%",0)) > 10000000 AND Sum(NVL("TOTAL 100%",0)) <= 15000000;
having Sum(NVL("TOTAL 100%,0)) <= 5000000;

 

 

T1:

locid&polid a s%key

A,

B,

LOCID,

POLID,

SICID,

SRCNAME

SUM(VALUE1),

SUM(VALUE2)

GROUP BY 

A,

B,

LOCID,

POLID,

SICID,

SRCNAME

;

Resident:

load

locid&polid a s%key

sum(value1) as total

sum(value3) as prem

resident

group by locid,polid;

 

 

 

Labels (1)
0 Replies