patients who gave birth

[Margaret]

When a baby is born at our hospital the mom's AbstractID is recorded in the baby's chart. I have the data in a .qvw like this.The patients with a value in Mom'sAbstractID are newborns. Two of them have the same value in Mom'sAbstractID because they are twins.

AbstractID	Name	Mom's AbstractID
1	Mary
2	Latisha
3	Elsa	2
4	Telsa	2
5	Liam	1
6	Jenny

I want to create a birth log that shows the names of the moms who gave birth and the details about their babies. I don't want to list patients who didn't have babies (whether or not they are newborns, I don't want them listed), like this. Note that the mom of twins is listed twice.

MomName	BabyName
Mary	Liam
Latisha	Elsa
Latisha	Telsa

How do I create a table with the names of the moms who gave birth (the moms who's AbstractID is contained in another patient's row)?

Thank you!!!

[Anonymous]

Have you tried a Mapping load using AbstractID and Name.

Then using an ApplyMap on Mom's AbstractID to give you the MomName.

[sunny_talwar]

Although I have not done this, but can we use Hierarchy Load here bill.markham‌?

[MK_QSL]

If you have this data in database, create these fields using SELF JOIN..

[Margaret]

I was hoping to keep all the patients in one table but maybe that isn't the best route...

[jayanttibhe]

Hi Margaret,

You can do Inner Join on the same table to get the desired result :

Use following code :

T1 :
LOAD * INLINE [
"AbstractID", "Name", "Mom's AbstractID"
1, Mary
2, Latisha
3, Elsa, 2
4, Telsa, 2
5, Liam, 1
6, Jenny
];
  NoConcatenate
  T2 :
Load
AbstractID as ID,
If(len(trim([Mom's AbstractID]))=0, Name) as MOM_Name
Resident T1
;
  T3:
Inner Join (T2)
Load [Mom's AbstractID] as ID,
If(len(trim([Mom's AbstractID]))>0, Name) as Baby_Name
Resident T1
;
  Drop Table T1;

[MarcoWedel · Accepted solution]

Hi,

one implementation of Sunny's Hierarchy solution could be:

tabPatients:
Hierarchy (AbstractID, [Mom's AbstractID], Name,,Name)
LOAD AbstractID,
    Name,
    [Mom's AbstractID]
FROM [https://community.qlik.com/thread/244636] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

[sunny_talwar]

I knew it, I knew it.... I need to learn this (I am smiling right now )