2 Replies Latest reply: May 26, 2015 4:01 PM by Joe San Pietro RSS

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

      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 #
      123456A
      123456B
      123456C

      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
      123456A3

       

      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.