3 Replies Latest reply: Apr 26, 2017 2:15 PM by Marco Wedel RSS

    Separate lists of value in different row

    Charlie Audebert

      I'm trying to transform my data to separate each element from a list in a different row as the example below.

       

      Input data:

       

      No, Serial Number

      1, 20;23

      2, 21-25

      3, 6-10, 12

      4, 3-5; 10-12

       

      Objective:

      No, Serial Number

      1, 20

      1, 23

      2, 21

      2, 22

      2, 23

      2, 24

      2, 25

      3, 6

      3, 7

      3, 8

      3, 9

      3, 10

      3, 12

      4, 3

      4, 4

      4, 5

      4, 10

      4, 11

      4, 12

       

       

      My problem is that there are two differents cases. Simple elements separated by a ';' and intervals with '-' that I have to complete.

       

      I succeeded in separating the simple elements separated by the ';' with the subfield() function:

       

      SubField("Serial Number", ';') as Number,

       

       

      Similarly for the intervals with the iterno() function:

       

      SubField("Serial Number", '-', 1) + iterno() -1 as Number

      while(SubField("Serial Number", '-', 1) + iterno() <= SubField("Serial Number", '-', 2) +1);

       

      But I do not find a solution to combine both to get the expected result.

       

      Thanks for your help.

       

      Charlie