Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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
Have you tried a Mapping load using AbstractID and Name.
Then using an ApplyMap on Mom's AbstractID to give you the MomName.
Although I have not done this, but can we use Hierarchy Load here bill.markham?
If you have this data in database, create these fields using SELF JOIN..
I was hoping to keep all the patients in one table but maybe that isn't the best route...
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;
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
I knew it, I knew it.... I need to learn this (I am smiling right now )