Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

combining duplicated rows

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 TypePrimary CesareanRepeat Cesarean
5/30/17100Cesarean--
5/30/17100--Y
5/31/17101Vaginal--
6/1/17102Cesarean--
6/1/17102-Y-
6/2/17103Vaginal--

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;

1 Solution

Accepted Solutions

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;

View solution in original post

3 Replies

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;

View solution in original post

Partner
Partner

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;

Creator II
Creator II

Thank you, Sunny! You are my hero!!