8 Replies Latest reply: Aug 3, 2017 12:28 PM by Amuktha Chakilam RSS

    SQL statement in Load (QlikSense)

    Amuktha Chakilam

      Hi all,

       

      I've the following table

       

      boberdoo:

      LOAD id,

          transaction_date,

          `b_lead_id_unique`,

          email,

          phone;

       

      SQL SELECT *

      FROM forms.`boberdoo_leads_master`

      ;

       

      Question: I need help to embed the following SQL Query into the Load statement. This query modifies the original table and adds column flag to the db table. Instead I want to add this column only to the boberdoo table in Qlik and not modify the table in database.


      select a.id, a.transaction_date, a.phone, a.email,

      case when a.phone is null and a.email is null then null

      when sum(case when b.phone is not null or c.email is not null then 1 else 0 end) > 0 then 'Duplicate' else 'Unique' end as flag

      from forms.`boberdoo_leads_master` a

      left join forms.`boberdoo_leads_master` b on (a.phone = b.phone and a.transaction_date > b.transaction_date)

      left join forms.`boberdoo_leads_master` c on (a.email = c.email and a.transaction_date > c.transaction_date)

      group by a.id, a.transaction_date, a.phone, a.email

       

       

       

      Thanks.

        • Re: SQL statement in Load (QlikSense)
          Michael Solomovich

          If your query works on SQL, you can use it as is in the Qlik script.  It's not going to modify the database table.

            • Re: SQL statement in Load (QlikSense)
              Rohit Kumar

              Hi Amuakha,

               

              The query you have written is a simple SQL query

              select a.id, a.transaction_date, a.phone, a.email,

              case when a.phone is null and a.email is null then null

              when sum(case when b.phone is not null or c.email is not null then 1 else 0 end) > 0 then 'Duplicate' else 'Unique' end as flag

              from forms.`boberdoo_leads_master` a

              left join forms.`boberdoo_leads_master` b on (a.phone = b.phone and a.transaction_date > b.transaction_date)

              left join forms.`boberdoo_leads_master` c on (a.email = c.email and a.transaction_date > c.transaction_date)

              group by a.id, a.transaction_date, a.phone, a.email

               

              So in Qlik , You first load the table from data source, by creating an ODBC connection and automatic script will be generated, now the condition you have written for outcome as

               

              left join forms.`boberdoo_leads_master` b on (a.phone = b.phone and a.transaction_date > b.transaction_date)

              left join forms.`boberdoo_leads_master` c on (a.email = c.email and a.transaction_date > c.transaction_date)

              group by a.id, a.transaction_date, a.phone, a.email

               

              It is kind of self join and filtering data on multiple conditions and having group by clause. So you may write it is

               

              Load

              *,

              '1' as Flag

              from

              boberdoo_leads_master


              WHERE


              Conditions of filtering


              GROUP BY

              transaction_date,

              phone,

              email

              ;


              ///////////

              case when a.phone is null and a.email is null then null

              when sum(case when b.phone is not null or c.email is not null then 1 else 0 end) > 0 then 'Duplicate' else

               

              For this , you can use IF statement in Qlik, you may read on community or in help how to use it

               

              ///////////////

               


              In qlik by load and select statement you can only fetch the data for modify the data in SQL server, you may use

              SQL write update or delete command or any instruction of SQL


              Please load the table first in Qlik and try to do what I have stated and let me know where you stcuk.


              Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

               

               

              Rohit Kumar