topic Re: Merging same rows in QlikView

Merging same rows

MK9885 — Wed, 25 Nov 2020 16:16:04 GMT

I have a straight table with 10+ dimensions, 5+ measures

When I select filters etc.. I get 3 rows.

2 rows are exactly the same but one of the measure has 0 value in row1 & in 2nd row it has 0 value

Ex:

Employees Amount Charge Amount remaining

Emp1 0 45678

Emp1 12345 0

I tried Aggr with IF, doesn't work...

=aggr(if(Sum({$<Filter= {'Charge', 'Charge Void'} >} Amount_Charge)<>0, Code), Code, other dimensions)

Above exp is only for Amount Charge column. I am not doing anything for Amount Remaining... not sure if I have to add 2nd calculated dim???

When I use above calculated dim, it gives removed 2nd row

I want both the rows but like below

Employees Amount Charge Amount remaining

Emp1 12345 45678

Any help of suggestion would be appreciated.

@sunny_talwar

Thanks.

Re: Merging same rows

sunny_talwar — Mon, 26 Aug 2019 18:49:11 GMT

Can you share a sample where we can see the issue?

Re: Merging same rows

MK9885 — Mon, 26 Aug 2019 19:37:53 GMT

Size is too large.. Even after reloading it to only current month.

Please see your inbox for attached image. Hopefully you can help me with looking at what I'm talking about?

Re: Merging same rows

MK9885 — Tue, 27 Aug 2019 13:08:35 GMT

I was able to solve the issue using a correct answer you gave to someone in community...

If(SubStringCount('|' & GetFieldSelections(Source, '|,|') & '|', '|Order|') = 1 and SubStringCount('|' & GetFieldSelections(Source, '|,|') & '|', '|Invoice|') = 1, Sum(OrderUnits) + Sum(InvoiceUnits),

If(Source='Order', Sum(OrderUnits),

If(Source = 'Invoice', Sum(InvoiceUnits))))

From https://community.qlik.com/t5/New-to-Qlik-Sense/If-Condition-for-Multiple-And-values-in-expression/td-p/1263133

So I'd have to modify it to a 7 line exp

Re: Merging same rows

sunny_talwar — Tue, 27 Aug 2019 13:10:52 GMT

Superb!!

Re: Merging same rows

MK9885 — Tue, 27 Aug 2019 13:15:06 GMT

But I've similar issue or maybe different cus I have a requirement where a value should not show in chart if both of them are satisfied.
Ex:

A user has both Charge & Adjustment for CodeABC

Same user just has Charge but no adjustment but for a different Code123

Both the items are true but CodeABC should not be shown in a chart where it is missing Adjustment.

Any help you can do?

Re: Merging same rows

MK9885 — Tue, 27 Aug 2019 15:11:19 GMT

@sunny_talwar

A general question..

Ex: I have a patient with Charge & Adjustment with key123

Same patient just has charge but no adjustment with key555

Now I want to show value only where there is a charge but no adjustment.

So I want to satisfy both conditions for same key but I'm having hard time to compare within same keyfield.