Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

laurischarf
Contributor II

Deriving fields from multiple resident tables in Load

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.

Tags (1)
1 Solution

Accepted Solutions

Re: Deriving fields from multiple resident tables in Load

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;

2 Replies

Re: Deriving fields from multiple resident tables in Load

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;

laurischarf
Contributor II

Re: Deriving fields from multiple resident tables in Load

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