topic Re: Identical rows in App Development

Identical rows

Vikash2024 — Wed, 12 Mar 2025 05:10:55 GMT

I have a table that have identical rows so how to handle the identical rows without losing the data.

My Query is when I create a KPI it will take only one value from that identical rows But i need all the value.
How to handle this type of problem?

Re: Identical rows

Chanty4u — Wed, 12 Mar 2025 05:24:44 GMT

Usually if you use

Sum(measure) will give you original result right ?

Or try this

CONCAT(DISTINCT YourField, ', ')

Or try this

SUM(AGGR(SUM(YourField), YourDimension))

Re: Identical rows

rajuamet — Wed, 12 Mar 2025 05:26:18 GMT

Hi @Vikash2024 , at the script level, to your table, add a column with "RowNo() as Row", this will help to handle the identical rows without losing the data.

Re: Identical rows

Vegar — Wed, 12 Mar 2025 05:56:09 GMT

The data is not lost. In Qlik will a table with identical row values only be displayed once, but if you make a KPI based on any field on those rows then it will consider both.

Consider thos data set.

LOAD * INLINE [

Fruit, Qty

Banana, 5

Banana,5

Banana, 5];

Using that data set.

Sum(Qty) = 15
Count(Fruit)=3
Concat(Fruit, ',')='Banana,Banana,Banana'

If you experience other then please explain and maybe add an example with a bit more details in order for us to help you better.

Re: Identical rows

Vegar — Thu, 13 Mar 2025 05:58:13 GMT

@Vikash2024 where you able to fix/solve your problem?

If so then please consider marking one or multiple of the responses as a solution. That will be helpful info for others with similar questions in the future.

Re: Identical rows

Vikash2024 — Thu, 13 Mar 2025 17:59:37 GMT

no still not

Re: Identical rows

Vegar — Fri, 14 Mar 2025 05:55:52 GMT

Please elaborate on your issue and add some more details about what your current situation is, some details about curren data, fields, calculations and output; and some details about expected output.

Re: Identical rows

Vikash2024 — Mon, 17 Mar 2025 05:18:56 GMT

Sure, these are the tables below images table 1 and table2 both have identical rows. In Table1 There are 3 rows with Invoice number INV001 and Table2 there are 5 rows with invoice no INV001. if i take sum(item net amount) Table1 is 5400.
and In Table2 sum(Item Net Amount) is 9000.
so here is my question I need the sum of Item net amount according to the table1 in Table2 also. like
Table1 : Sum(Item Net Amount) = 5400
Table 2 : Sum(Item Net Amount) = 5400

I hope you will understand if not please do let me know.

Re: Identical rows

Vegar — Mon, 17 Mar 2025 05:38:33 GMT

I do not completly ubderstand.

How would you know that you could sum all the three identical rows in your first example, but only three out of five rows in the second example?

How would you go about to identify which rows are identical or not? Will all rows with the same invoice number be identical, or is it all rows with the same combination of invoice number and item code? Or can any of the columns differ so you would have to check uniqueness by looking at the combination of values in all columns?

Re: Identical rows

rajuamet — Mon, 17 Mar 2025 06:08:22 GMT

Hi @Vikash2024 , Is there a logic behind why we have 3 identical rows in table 1 and 5 identical rows in table 2.

Also, If table 1 gives the expected output then that field can be used for measure right!

I suspect that there will be an unique ID for the 3 rows if you are considering to use only the 3 rows.

We are missing something here.

As @Vegar mentioned, how would you know that all three identical rows should be summed.

Re: Identical rows

Vikash2024 — Mon, 17 Mar 2025 07:45:07 GMT

In the images that I shared with you. I loaded both the tables in Qlik sense. and compare when i take the KPI's for specific Invoice_no_ = 001 then
sum(Item Net Amount) = 5400 in Table1
But When I will take Sum(Item Net Amount) in Table2. It will show me 9000.
My question is that when I took filter of Inovice_no_ = INV001. then in the both table of Sum(Item Net Amount) would be the same 5400.

Re: Identical rows

Vikash2024 — Mon, 17 Mar 2025 07:45:17 GMT