4 Replies Latest reply: Oct 10, 2016 8:19 AM by Matthias Siegert RSS

    Insert into Select with Qlik

    Matthias Siegert

      Hi,

       

      I am totally new with Qlik but have some experiences with relational Databases, now I am faced with the first challenge within Qlik-Sense. This is my first post, so please excuse if I do something wrong.

       

      I have two sample tables, that cannot be joined, but I want to merge the data from one of the tables into the other table. Both tables come from two different Data Sources.

       

      The first table contains Events. An Event can be e.g. an examination of a Patient:

       

      CREATE TABLE [dbo].[Event](

       

      [EventDesc] [nvarchar](max) NOT NULL,

       

      [EventDate] [date] NULL,

       

      [EventTime] [time](7) NULL,

       

      [EventYear] [smallint] NULL,

       

      [EventMonth] [nvarchar](50) NULL,

       

      [EventDay] [smallint] NULL,

       

      [EventPatientID] [bigint] NULL

       

      ) ON [PRIMARY]

       

      The second Table contains Patients with their Start-Date and a registering of a new Patient can also be understand as an event:

       

       

      CREATE TABLE [dbo].[Patient](

       

      [Patient_ID] [bigint] NOT NULL,

       

      [Patient_Date] [date] NULL,

       

      [Patient_Time] [time](7) NULL,

       

      [Patient_Year] [smallint] NULL,

       

      [Patient_Month] [nvarchar](50) NULL,

       

      [Patient_Day] [smallint] NULL

       

      ) ON [PRIMARY]

       

       

      Within SQL-Server I can merge the entries of the second table as Events in the first table with "Insert Into Select".

      So I have as result only the Events-Table with all the information from the Patient-Table as well. The Patient Date and Time is

      the registering time and in this case the time for my Event "Patient_Registration". So In SQL it would work like this:

       

      INSERT INTO Event (EventDesc, EventDate, EventTime, EventYear, EventMonth, EventDay, EventPatientID)

       

      SELECT 'Patient_Register', Patient_Date, Patient_Time, Patient_Year, Patient_Month, Patient_Day, Patient_ID

       

       

      FROM Patient p1

       

      WHERE NOT EXISTS (SELECT EventPatientID FROM Event e1 WHERE e1.EventPatientID = p1.Patient_ID);

       

      How I can solve this issue within the Data load editor in Qlik-Sense? Until now I have two divided load Statements for the two tables but I don't know how to combine them to have one table as the result. There exist no join-possibility and the column-Names in the two tables are different.

      An Approach would be helpful or do I have to do this in the Backend-DB (SQL-Server) before loading the Data to Qlik?

       

      Thanks for help and Best Regards,

      Matthias

        • Re: Insert into Select with Qlik
          Rohit Kumar

          Hi Matthias,

           

          It was a good way how you explained your concern.

           

          Now to perform the same logic in Qlik script , What you need to do ,


          Event:

          load

          *

          from Event;

           

          concatenate

           

          Patient:

          load

          *

          from

          Patient;

          =================================================================

          Second way , I think you may apply join between two tables as :


          Event:

          load

          *,

          EventPatientID as Patient_ID

           

          from Event;

           

          concatenate

           

          Patient:

          load

          *

          from

          Patient;


          when you make column names same for two tables it will join the two tables automatically , but if more than two fields has same name so it will create synthetic key which can slow your performance. try to avoid it please.



          Third way:


          Event:

          load

          *,

          EventPatientID as Patient_ID

           

          from Event;

           

          inner join

           

          Patient:

          load

          *

          from

          Patient;



          you may use any of join depends on your schema .


          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


          • Re: Insert into Select with Qlik
            Fernando Keuroglian

            Hi Matthias

             

            You can use LEFT JOIN to do this

             

            see this post to help with examples

             

            Understanding Join, Keep and Concatenate

             

            Hope Help

             

            Fernando K.

            • Re: Insert into Select with Qlik
              Sébastien Fatoux

              Hi, Try this to have the same résultat as SQL Server

               

              Event:

              LOAD

                  EventDesc,

                  EventDate,

                  EventTime,

                  EventYear,

                  EventMonth,

                  EventDay,

                  EventPatientID

              FROM Event;

               

              Concatenate

              LOAD

                  'Patient_Register' as EventDesc,

                  Patient_Date        as EventDate,

                  Patient_Time       as EventTime,

                  Patient_Year        as EventYear,

                  Patient_Month     as EventMonth,

                  Patient_Day         as EventDay,

                  Patient_ID           as EventPatientID

              FROM Patient

              WHERE not exists(EventPatientID, Patient_ID);