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,