combining duplicated rows

[Margaret]

I have a QlikView application that, for births prior to a specific date, looks at nursing documentation to see whether a cesarean was a woman's first (Primary Cesarean) or subsequent (Repeat Cesarean). After that date, it looks at Provider (Dr/midwife) documentation for the same information.

Currently the data looks as below. But when I do set analysis it doesn't see the "Y"'s as related to the Cesareans presumable because the "Y" is in a different row than "Cesarean", despite them having the same MomID.

I've tried to use Alt and If(IsNull... (see last LOAD statement) to consolidate the rows to no avail. What am I doing wrong?

Thank you!!

Birth Date	Mom ID	Birth Type	Primary Cesarean	Repeat Cesarean
5/30/17	100	Cesarean	-	-
5/30/17	100	-	-	Y
5/31/17	101	Vaginal	-	-
6/1/17	102	Cesarean	-	-
6/1/17	102	-	Y	-
6/2/17	103	Vaginal	-	-

BirthTypeTemp:

LOAD

MomsAbstractID,

If((TempType = 'Vaginal' or TempType = 'Vag'), 'Vag',

  If((TempType = 'Repeat Cesarean' or TempType = 'Primary Cesarean' or TempType = 'Cesarean' or TempType = 'Ces'), 'Ces')) as DocType,

If(TempType = 'Primary Cesarean', 'Y') as DocPrim,

If(TempType = 'Repeat Cesarean', 'Y') as DocRep

Resident ProviderDocumentation;

Concatenate

LOAD

MomsAbstractID,

TempPrim,//need on same row

TempRep//need on same row

Resident MomTransferSummaries;

BirthTypeTemp2:

LOAD

MomsAbstractID,

DocType,

ALT(DocPrim,TempPrim) as Temp2Prim,

ALT(DocRep,TempRep) as Temp2Rep

Resident BirthTypeTemp;

[sunny_talwar · Accepted solution]

I am not really sure I understand your code, but the idea is that you need to use a Group by statement with may be Concatenate like this

LOAD BirthDate,

     MomID,

     Concat(BirthType) as BirthType,

     Concat([Primary Cesarean]) as [Primary Cesarean],

     Concat([Repeat Cesarean]) as [Repeat Cesarean]

Resident....

Group By BirthDate, MomID;

[felipedl]

Hi Margaret

Without seeing the actual data your tables have, I'm guessing that instead of concatenating the two tables,hence probably giving you the multiple lines, you should join them.

Try the following:

BirthTypeTemp:

LOAD

MomsAbstractID,

If((TempType = 'Vaginal' or TempType = 'Vag'), 'Vag',

  If((TempType = 'Repeat Cesarean' or TempType = 'Primary Cesarean' or TempType = 'Cesarean' or TempType = 'Ces'), 'Ces')) as DocType,

If(TempType = 'Primary Cesarean', 'Y') as DocPrim,

If(TempType = 'Repeat Cesarean', 'Y') as DocRep

Resident ProviderDocumentation;

Left Join (BirthTypeTemp)

LOAD

MomsAbstractID,

TempPrim,//need on same row

TempRep//need on same row

Resident MomTransferSummaries;

BirthTypeTemp2:

LOAD

MomsAbstractID,

DocType,

ALT(DocPrim,TempPrim) as Temp2Prim,

ALT(DocRep,TempRep) as Temp2Rep

Resident BirthTypeTemp;

[Margaret]

Thank you, Sunny! You are my hero!!