Qlik Variable

QlikBeginner1 — Sat, 16 Nov 2024 18:45:52 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

Re: Qlik Variable

QlikBeginner1 — Mon, 20 Apr 2020 12:03:05 GMT

Is this something that is not achievable in Qlik Sense? This was a quick and urgent request which was why its being loaded via a SQL query. using this : Let vDateTO = Date(Max([dp.EffectiveDate],'DD-MM-YYYY')); does not error the app from loading, however I am unable to see the variable in the list of variables? However if i include that in a measure in the front end, I can see the value of the date I want..

Thanks in advance experts!

topic Qlik Variable in App Development

Qlik Variable

Re: Qlik Variable