5 Replies Latest reply: Mar 28, 2016 1:59 PM by Sangram Reddy RSS

    How to identify the duplicate data from a list

    Jing Yang

      Hi Experts,

       

      I have a set of data like below:

      StudentID

      ItemID

      1

      1001

      1

      1002

      1

      1003

      1

      1004

      2

      1004

      2

      1003

      2

      1002

      2

      1001

      3

      1001

      3

      1002

      3

      1003

      3

      1005

      4

      1001

      4

      1002

      4

      1005

      4

      1003

      5

      1001

      5

      1005

      5

      1003

      5

      1004

      6

      1007

      6

      1005

      6

      1001

      6

      1004

       

      As you can see,

      StudentID 1's item list: 1001, 1002, 1003, 1004

      StudentID 2's item list: 1004, 1003, 1002, 1001

      They actually have the same list of items.

       

      I need to change the above data like below:

       

      ResultID

      Item1

      Item2

      Item3

      Item4

      # students

      1

      1001

      1002

      1003

      1004

      2

      2

      1001

      1002

      1003

      1005

      2

      3

      1001

      1005

      1003

      1004

      1

      4

      1007

      1005

      1001

      1004

      1

       

       

      We can filter and count the duplicate lists and have a list of items for each unique combination in code like Java, but I am wondering if we can do it in qlik sense data load script.

       

      Any help will be appreciated.

       

      Thanks,

      Jing