2 Replies Latest reply: Aug 7, 2017 11:14 AM by Lauri Scharf RSS

    Deriving fields from multiple resident tables in Load

    Lauri Scharf

      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.