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;
