Hello! I am trying to add variable with if/when case where the return depends on the value in another field. For example, I am basically trying to tag a the value in another field. If Sum(1) in Conversions table = 0,
I want the value in another field to be "0", if sum(1)= 1, "1 ", if sum(1) >1, "1+".
I am new to Qlik Sense. Thank you!
LIB CONNECT TO 'SQL Server';
SQL
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#Opens') IS NOT NULL
DROP TABLE #Opens
IF OBJECT_ID('tempdb..#Clicks') IS NOT NULL
DROP TABLE #Clicks
IF OBJECT_ID('tempdb..#Conversions') IS NOT NULL
DROP TABLE #Conversions
Declare @ConversionScore varchar(25)
set @ConversionScore = '0'
select sum(1) FROM [BarnesNobleReporting].[dbo].[Conversions]
where sum(1) = 0
SELECT [SubscriberKey]
,SUM(Case When IsUnique = 'True' THEN 1 ELSE 0 END) AS UniqueClicks
,SUM(1) AS TotalClicks
,MAX(EventDate) AS RecentClickDate
Into #Opens
FROM [BarnesNobleReporting].[dbo].[Clicks]
GROUP BY SubscriberKey
SELECT [SubscriberKey]
,SUM(Case When IsUnique = 'True' THEN 1 ELSE 0 END) AS UniqueOpens
,SUM(1) AS TotalOpens
,MAX(EventDate) AS RecentOpenDate
Into #Clicks
FROM [BarnesNobleReporting].[dbo].[Open]
GROUP BY SubscriberKey
SELECT [SubscriberKey]
,SUM(1) AS Conversions
,MAX(EventDate) AS RecentConversionDate
,sum(convert(money,replace(replace(replace(ConversionData, '''', ''), '<data><unit name=Dollars value=', ''), ' /></data>', ''))) as ConversionAmount
,@ConversionScore AS ConversionSegment
Into #Conversions
FROM [BarnesNobleReporting].[dbo].[Conversions]
GROUP BY SubscriberKey
SELECT S.[SubscriberKey]
,SUM(1) AS Deliveries
Into #Deliveries
FROM [BarnesNobleReporting].[dbo].[Sent] S
LEFT JOIN [BarnesNobleReporting].[dbo].[Bounces] B ON S.SubscriberKey = B.SubscriberKey AND S.SendID = B.SendID AND S.BatchID = B.BatchID
WHERE B.SendID iS NULL
GROUP BY S.SubscriberKey
SELECT S.[SubscriberKey]
,UniqueOpens AS UniqueOpens
,TotalOpens AS TotalOpens
,UniqueClicks AS UniqueClicks
,TotalClicks AS TotalClicks
,Conversions AS Conversions
,ConversionAmount
,Deliveries
,RecentConversionDate AS RecentConversionDate
,RecentClickDate AS RecentClickDate
,RecentOpenDate AS RecentOpenDate
,@ConversionScore AS ConversionSegment
FROM #Deliveries S
LEFT JOIN #Opens O ON S.SubscriberKey = O.SubscriberKey
LEFT JOIN #Clicks C ON S.SubscriberKey = C.SubscriberKey
LEFT JOIN #Conversions C2 ON S.SubscriberKey = C2.SubscriberKey;
