1 Reply Latest reply: Jun 20, 2017 6:56 PM by David Forest RSS

    Add Variable where Return Depends on Another Field's Value

    dsgwe efew

      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;

        • Re: Add Variable where Return Depends on Another Field's Value
          David Forest

          Not sure why you are building temp tables in SQL using Qlik as it is unnecessary. Qlik will create a memory resident table that can be reused.

          In your example, you could just place joins between the statements as well...

           

          You can use If in a LOAD statement above your SQL, Qlik will perform the LOAD using the SQL following it. takes a while to get used to, but it eliminates the need for temp tables some times.

           

          [Conversions]:

          LOAD *,

          If(Conversions=1,'1','1+') AS NewFlag;

          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

          FROM [BarnesNobleReporting].[dbo].[Conversions]

          GROUP BY SubscriberKey