Deriving fields from multiple resident tables in Load

[Lauri]

I'm trying to derive a few calculated fields during a load, and to group on an ID field. The calculated fields are based on one resident table, and the ID is based on another resident table. The two resident tables do not have fields in common, but there is another table that I can use to join them.

My resident tables are:

• PartA (fields: PatientID, PartAKey)
• PartA_DX (fields: PartAKey, DX)
• DX_Groups (fields: DX, DX_ccs_category)

I want to create a Diabetes field and a Hypertension field that indicates yes or no for each patient ID. My first thought was to do this:

Load

PatientID, 

Max(If(match(DX_ccs_category, '49', '50')>0, 1, 0)) as Diabetes,

Max(If(match(DX_ccs_category, '88', '89')>0, 1, 0)) as Hypertension

Resident DX_Groups

Group By PatientID;

Inner join (PartA_Dx) Load PartAKey Resident PartA_Dx;

Inner Join (PartA) Load ID Resident PartA;

But this does not work; Qlik says 'field not found - PatientID. I suspect there is a good way to achieve my goal... Any tips would be great! Thank you.

[rubenmarin · Accepted solution]

Hi Lauri, can you try this?

// Join patient data in a temporal table

joinData:

Load

  PatientID,

  PartAKey,

Resident  PartA;

// Add PartA_DX data to temporal table

Inner Join (joinData) Load PartAKey, DX Resident PartA_DX ;

// Add DX_Groups data to temporal table

Inner Join (joinData) Load PartAKey, DX Resident DX_Groups ;

// Create calculated fields in final table

Data:

LOAD PatiendID,

  Max(If(match(DX_ccs_category, '49', '50')>0, 1, 0)) as Diabetes,

  Max(If(match(DX_ccs_category, '88', '89')>0, 1, 0)) as Hypertension

Resident joinData

Group By PatientID;

// Delete temporal table

DROP table joinData;

[Lauri]

Brilliant, Ruben! Clearly, I had misunderstood how to do joins. Thank you so much for your quick help.