1 Reply Latest reply: Feb 23, 2016 2:39 AM by Raajesh Nagarajan RSS

    Repeating rows n times based on column value

      Hi Qlikview masters. I'm having difficulty extracting and transforming an excel file into Qlikview. I have two columns, Container No and Order No. Below is a sample of how the file looks:

      Container NoOrder No
      C1551604 552487 569871 598741
      C2551603 524187
      C3603874 602458 532145 512367 654871 874569
      C1524165
      C4551604 552487 569871 598741

       

      The order numbers are always 6 characters long and are always separated by a space. Order numbers can be split into more than one container (e.g. C1 and C4.).

      Containers can be used multiple times, with different loads (e.g. C1 with 4 orders and C1 with 1 order).

      I need to create a qvd file of the above table, but in the below format:

      Container NoOrder No

      C1

      551604
      C1552487
      C1569871
      C1598741
      C2551603
      C2524187
      Etc...

       

      The below formula calculates the number of orders in the Order No Field:

           round((len([Order No])-len([Order No])/7)/6,1.00)

      And I know I should use a for loop which runs from the first container number to the last (the count of rows in the file), and within this loop I should have a loop that runs based on the number of orders within the container. I created a macro in VB which can do this, but getting this macro in QV is quite troublesome. File attached for reference.

       

      Can you please assist me with this issue? Would really appreciate the help.