Variable via SQL

QlikBeginner1 — Sat, 16 Nov 2024 18:45:56 GMT

Hello.

I have got an app which has a SQL Select query to select fields and populate a table for a report.

For the front page of the NPrinting report, I need to create a variable for the max date for a specific field. However, I do not know how to create a variable using SQL script.

Load*;

SQL select dp.Number
,dp.InceptionDate
,dp.EffectiveDate
,dp.ExpiryDate
,dp.CancellationDate
,datediff(year,dp.InceptionDate,dp.EffectiveDate) as TermNumber
,dlob.LobCd
,dlob.LobDesc
,SUM(fp.GWP) AS GrossWrittenPremium
,SUM(fp.NWP) AS NetWrittenPremium
,SUM(fp.Commission) AS Commission

from [GBQPS1SDA01DMA].[cdm].[FactPrm] fp
join [GBQPS1SDA01DMA].[cdm].[DLOB] dlob on fp.FKLineOfBusinessOID = dlob.LineOfBusinessOID
join [GBQPS1SDA01DMA].[cdm].[Dim] dp on fp.FKOID = dp.OID
and dp.CurrentRecordIndicator = '1'
join [GBQPS1SDA01DMA].[cdm].[DIMPrT] dpt on fp.FKTrOID = dpt.PrmTOID
and dpt.CurrentRecordIndicator = '1'

where 1=1
and dlob.LobCd IN ('B0100', 'B0098', 'B0099')
AND dp.EffectiveDate = dp.ExpiryDate

GROUP BY dp.Number
,dp.InceptionDate
,dp.EffectiveDate
,dp.ExpiryDate
,dp.CancellationDate
,datediff(year,dp.InceptionDate,dp.EffectiveDate)
,dlob.LobCd
,dlob.LobDesc
HAVING SUM(fp.GWP) <> 0;

exit script;

I wanted to create a variable to pass through to NPrinting which gives me the Max value of EffectiveDate, so essentially;

Let vDateTO = Date(Max([dp.EffectiveDate],'DD-MM-YYYY')); - however this seems to not work,

Thanks

topic Variable via SQL in App Development

Variable via SQL