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,
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..
