Concatenate two tables and update one table depending on entries of the second table

[#1]

Hi,

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]

So to merge the Patient-Events into the Event-Table it works like this 

(Thanks to Sébastien Fatoux) :

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

As the result I have the Event-Table with Patient-Registrations (Sorted in Event-Time-Order).

Now I need to assign the EventPatientID of the PatientRegistration to all examinations, that have been done with this patient, before the next Patient is registered. So for all examinations, which come after the Patient-Registration before the next Patient-Registration. So that I get as result the following table:

How I can solve this issue within the Data load editor in Qlik-Sense? For relational Databases I would need to update the EventPatientID column, but as I understand, the dynamical update is not available for Qlick-sense until now. Is there an approach, how to solve such an “Update-Problem”? I found some examples in the Community, but I think in my example the challenge is, that there is no join possibility, because the dependency between the PatientRegistrations and the corresponding Examinations is only the timeframe.

Thanks for help and Best Regards,

Matthias