Insert into Select with Qlik

[#1]

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

[rohitk1609]

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

[fkeuroglian]

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.

[sfatoux72 · Accepted solution]

‌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);

[#5]

Thanks very much, it works now..