Forum Stats

  • 3,769,238 Users
  • 2,252,937 Discussions
  • 7,874,957 Comments

Discussions

Logic to Lookup a Reference table and Insert Multiple rows ?

Gururaj0-Oracle
Gururaj0-Oracle Member Posts: 78 Employee
edited Apr 10, 2020 9:24AM in Data Integrator

Hi ODI folks,

I have Table A, Table B and Table C. Doing a lookup for Columns 1,2,3,4,5( symptoms of a patient, value would be 1 or 0 (yes or no response) from A on B ( which are essentially rows in Table B ). In other words if any patient has multiple symptom match then I need to insert  that in to Table C for each symptom. I don't think my SymptomConverter function would return multiple values.  How to achieve this ? If you have a different approach please comment as well.

Table A is for Patients, Table B is set of well defined Symptoms (Will not change, static table with only few rows) Table C is the Target containing for internal use.

   pastedImage_4.png

Table B

pastedImage_5.png

SymptomConverter

CASE

WHEN ($1 = '1') THEN 'Cough'

WHEN ($2 = '1') THEN 'Fever

WHEN ($3 = '1') THEN 'Headache'

WHEN ($4 = '1') THEN 'Sore Throat'

WHEN ($5 = '1') THEN 'Fatigue'

ELSE NULL END

Regards

Gururaj

Tagged:

Best Answer

  • XavierGrosfils
    XavierGrosfils Member Posts: 155 Blue Ribbon
    edited Apr 9, 2020 6:15AM Accepted Answer

    Hello,

    Secret would be the "Unpivot" Component.

    Your fonction will not do what you expect...

    In a simplified exemple (I'm lazy)

    pastedImage_0.png

    In the Unpivot, your create your conversion to name

    pastedImage_1.png

    The filter is jsut to filter symptoms not "active"

    pastedImage_2.png

    Then a simple join with your symptons tables (or lookup if you prefer) to get the symptoms id.

    You could also directly map to the symptom id in the unpivot to save some trouble.

    Hope it helps.

    Xavier

Answers

  • XavierGrosfils
    XavierGrosfils Member Posts: 155 Blue Ribbon
    edited Apr 9, 2020 6:15AM Accepted Answer

    Hello,

    Secret would be the "Unpivot" Component.

    Your fonction will not do what you expect...

    In a simplified exemple (I'm lazy)

    pastedImage_0.png

    In the Unpivot, your create your conversion to name

    pastedImage_1.png

    The filter is jsut to filter symptoms not "active"

    pastedImage_2.png

    Then a simple join with your symptons tables (or lookup if you prefer) to get the symptoms id.

    You could also directly map to the symptom id in the unpivot to save some trouble.

    Hope it helps.

    Xavier

  • Gururaj0-Oracle
    Gururaj0-Oracle Member Posts: 78 Employee
    edited Apr 9, 2020 10:24PM

    Thank you, just started with ODI few months back, haven't used Unpivot. I think it is working, running the SQL it generated. Unable to test it fully due to some data issues. Wondering why I got the Filter after the Lookup :-) yeah the join was spilling out NULL records, no idea how to avoid that. Anyways your suggested solution works.

    pastedImage_0.png

  • Gururaj0-Oracle
    Gururaj0-Oracle Member Posts: 78 Employee
    edited Apr 10, 2020 9:24AM

    Hurrah...Loaded !  Below are fake Data.. hence not masking.

    pastedImage_0.png