topic Re: help with Group By in Connectivity & Data Prep

help with Group By

QlikBeginner1 — Thu, 23 Dec 2021 14:23:57 GMT

Hey all!

I have this SQL code, and been trying to interperate this in Qlik, and having issues!

select dp.PolicyNumber
,dp.PolicyInceptionDate
,dp.PolicyEffectiveDate
,dp.PolicyExpiryDate
,dp.PolicyCancellationDate
,datediff(year,dp.PolicyInceptionDate,dp.PolicyEffectiveDate) TermNumber --this is derived so not actually as per Cogen
,dlob.SAPLobCode
,dlob.SapLobDescription
,SUM(fp.GrossWrittenPremium) AS GrossWrittenPremium
,SUM(fp.NetWrittenPremium) AS NetWrittenPremium
,SUM(fp.Commission) AS Commission

from [cdm].[FactPremium] fp
join [cdm].[DimLineOfBusiness] dlob on fp.FKLineOfBusinessOID = dlob.LineOfBusinessOID
join [cdm].[DimPolicy] dp on fp.FKPolicyOID = dp.PolicyOID
and dp.CurrentRecordIndicator = '1'
join [cdm].[DimPremiumTransaction] dpt on fp.FKTransactionOID = dpt.PremiumTransactionOID
and dpt.CurrentRecordIndicator = '1'

where 1=1
and dlob.SAPLobCode IN ('B0100', 'B0098', 'B0099')
AND dp.PolicyEffectiveDate = dp.PolicyExpiryDate

GROUP BY dp.PolicyNumber
,dp.PolicyInceptionDate
,dp.PolicyEffectiveDate
,dp.PolicyExpiryDate
,dp.PolicyCancellationDate
,datediff(year,dp.PolicyInceptionDate,dp.PolicyEffectiveDate)
,dlob.SAPLobCode
,dlob.SapLobDescription

HAVING SUM(fp.GrossWrittenPremium) <> 0

I have these tables loaded via QVD files. I have started building this in the back end and got this error message? Aggregation expressions required by GROUP BY clause.

This is what I have so far, any help at all would be much appreciated, 🙂

LOAD *

FROM [lib://QVD/DMA\FactPremium.qvd]
(qvd);

JOIN

LOAD
LineOfBusinessOID as FKLineOfBusinessOID
,*
FROM [lib://QVD/DMA\DimLineOfBusiness.qvd]
(qvd);

Join

LOAD
PolicyOID as FKPolicyOID
,*
FROM [lib://QVD/DMA\DimPolicy.qvd]
(qvd)
WHERE CurrentRecordIndicatorPolicy = '1'
AND PolicyEffectiveDate = PolicyExpiryDate;

JOIN

LOAD
TransactionOID as FKTransactionOID
,*
FROM [lib://QVD/DMA\DimTransaction.qvd]
(qvd)
Where CurrentRecordIndicatorTransaction = '1'

GROUP BY PolicyNumber,PolicyInceptionDate, PolicyEffectiveDate, PolicyExpiryDate, PolicyCancellationDate, SAPLOBCode, SAPLOB;

Re: help with Group By

martinpohl — Thu, 19 Mar 2020 15:17:07 GMT

delete the group by statement at the end.

Yor need an aggregation in an aggregation statement

example:

load

sum(Value) as Sumvalue,

CustomerId

from datasource

group by CustomerId;

but when there is no aggregation in the script you don't need a group by

Regards

Re: help with Group By

QlikBeginner1 — Thu, 19 Mar 2020 15:41:23 GMT

How would I achieve the output I wanted with the SQL code i attached? As when I do simple joins to the tables without grouping by Policy numbers etc, i am not getting the output I require. Any suggestions?

I tried to include aggregations as shown below, but getting this error message:

The following error occurred:

Invalid expression

The error occurred here:

LOAD SUM(GrossWrittenPremium) as GrossWrittenPremiumSum ,SUM(NetWrittenPremium) as NetWRittenPremiumSum ,SUM(Commission) as CommissionSum ,* FROM [lib://QVD/DMA\FactPremium.qvd] (qvd)

The following error occurred:

Invalid expression

The error occurred here:

LOAD
SUM(GrossWrittenPremium) as GrossWrittenPremiumSum
,SUM(NetWrittenPremium) as NetWRittenPremiumSum
,SUM(Commission) as CommissionSum
,*

FROM [lib://QVD/DMA\FactPremium.qvd]
(qvd);

JOIN

LOAD
LineOfBusinessOID as FKLineOfBusinessOID
,*
FROM [lib://QVD/DMA\DimLineOfBusiness.qvd]
(qvd);

Join

LOAD
PolicyOID as FKPolicyOID
,*
FROM [lib://QVD/DMA\DimPolicy.qvd]
(qvd)
WHERE CurrentRecordIndicatorPolicy = '1'
AND PolicyEffectiveDate = PolicyExpiryDate;

JOIN

LOAD
TransactionOID as FKTransactionOID
,*
FROM [lib://QVD/DMA\DimTransaction.qvd]
(qvd)
Where CurrentRecordIndicatorTransaction = '1'

GROUP BY PolicyNumber,PolicyInceptionDate, PolicyEffectiveDate, PolicyExpiryDate, PolicyCancellationDate, SAPLOBCode, SAPLOB;

Re: help with Group By

martinpohl — Thu, 19 Mar 2020 15:48:10 GMT

now you do a sum and you need a group by.

But it won't work. You load * (all fields). so each line value will stay in a single line.

You have to list all dimension fields and use them by group by.

BUT

why to aggregate in script ?

use sum(GrossWrittenPremium) in a chart (or a table) and you will have the result.

Re: help with Group By

QlikBeginner1 — Thu, 19 Mar 2020 16:11:42 GMT

Thanks for your patience Martinpohl. Appreciate the help, I am still struggling to figure it out. I only included the dimensions I required, and still coming up with the error message. Could you show me an example?

LOAD
FKTransactionOID,
FKPolicyOID,
FKLineOfBusinessOID,
GrossWrittenPremium,
NetWrittenPremium,
Commission
FROM [lib://QVD/DMA\FactPremium.qvd]
(qvd);

JOIN

LOAD
LineOfBusinessOID as FKLineOfBusinessOID,
SAPLobCode,
SAPLOB
FROM [lib://QVD/DMA\DimLineOfBusiness.qvd]
(qvd)
WHERE SAPLobCode = 'B0100, B0098, B0099';

Join

LOAD
PolicyOID as FKPolicyOID,
PolicyNumber,
PolicyInceptionDate,
PolicyEffectiveDate,
PolicyExpiryDate,
PolicyCancellationDate
FROM [lib://QVD/DMA\DimPolicy.qvd]
(qvd)
WHERE CurrentRecordIndicatorPolicy = '1'
AND PolicyEffectiveDate = PolicyExpiryDate;

JOIN

LOAD
TransactionOID as FKTransactionOID
FROM [lib://QVD/DMA\DimTransaction.qvd]
(qvd)
Where CurrentRecordIndicatorTransaction = '1'

GROUP BY PolicyNumber,PolicyInceptionDate, PolicyEffectiveDate, PolicyExpiryDate, PolicyCancellationDate, SAPLOBCode, SAPLOB;

Re: help with Group By

QlikBeginner1 — Thu, 19 Mar 2020 16:50:09 GMT

Anyone? It might be so small and simple to most but i have only recently started developing in Qlik and still understanding syntax and how to code, but this would really help me get this finished, please 🙂

Re: help with Group By

QlikBeginner1 — Fri, 20 Mar 2020 09:54:47 GMT

No one can help?

Re: help with Group By

MikeW — Sat, 21 Mar 2020 15:29:30 GMT

The Group By is acting on the LOAD statement just before it, not the result of all the joins that you have made.

The proper syntax is like this...

[UnaggregatedTable]:

NoConcatenate

LOAD * blah * FROM [...] (qvd) WHERE ...;

JOIN([UnaggregatedTable])

LOAD * blah * FROM [...] (qvd) WHERE ...;

JOIN([UnaggregatedTable])

LOAD * blah * FROM [...] (qvd) WHERE ...;

[AggregatedTable]

NoConcatenate

LOAD *

WHERE

GrossWrittenPremiumSum<>0

;

LOAD

PolicyNumber,PolicyInceptionDate, PolicyEffectiveDate, PolicyExpiryDate, PolicyCancellationDate, SAPLOBCode, SAPLOB,

SUM(GrossWrittenPremium) as GrossWrittenPremiumSum,

SUM(NetWrittenPremium) as NetWRittenPremiumSum,

SUM(Commission) as CommissionSum

RESIDENT

[UnaggregatedTable]

GROUP BY

PolicyNumber,PolicyInceptionDate, PolicyEffectiveDate, PolicyExpiryDate, PolicyCancellationDate, SAPLOBCode, SAPLOB;

DROP TABLE [UnaggregatedTable];

So basically join it together in a table, then resident load the from that table and do the sum/group by there. Then use a preceding load to replicate the HAVING condition.