Creating a field by summing the amount of duplication of another field across lines

[#1]

Hello All,

My Qlikview document is for shipping statistics, and my source table has duplicate lines. For example, we have SO# 123456. SO#123456 has 3 lines on it

SO#	Item #
123456	A
123456	B
123456	C

This is fine for my QVD. However, I want to be able to add a third field which I will name NbrOfLines onto each row so that when I create any expressions using DISTINCT, I will know the amount of times this SO# is duplicated in the database.

Desired Result:

Max(Distinct SO#)

SO#	Item #	NbrOfLines
123456	A	3

I have been doing a lot of searching of how to tackle this. Initially I was thinking an If expression in the load script, but I am having trouble with my approach. Perhaps constructing a variable would be useful. I have already added the RowNo() as LineNo into my load script, and in Pseudo code I am thinking If(SO# of LineNo(1)= SO# of LineNo(2) then sum the count of rows.) I am lost on how to structure at this point.

[maxgro · Accepted solution]

with these test data

test:

load * inline [

SO#, Item #

123456, A

123456, B

123456, C

1, A

1, B

2,A

2,B

2,C

2,D

];

you can get the result with an expression (red rectangle), there is no need to add a third field

if you still want to add the field

test:

load * inline [

SO#, Item #

123456, A

123456, B

123456, C

1, A

1, B

2,A

2,B

2,C

2,D

];

left join (test)

load SO#, count([Item #]) as NbrOfLines Resident test group by SO#;

[#3]

Thank you Massimo! Looks like I need to study more on the application of Total and Group By's in the load script. Thanks for the help.