Identical rows

[Vikash2024]

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?

[Chanty4u]

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))

[rajuamet]

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.

[Vegar]

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.

[Vegar]

@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.

[Vikash2024]

no still not 

[Vegar]

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.

[Vikash2024]

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.

[Vegar]

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?

[rajuamet]

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.