Is there a UniVerse BASIC command like Excel TRANSPOSE?

I have a statistics program that builds an array with <1,POS>= sku and <2,POS>=qty.  The output would often have 8-9k SKUs.

When I dump it out to a sequential file, I want to output the data as  a sinlge row for each sku in the format of SKU:<delimiter>:QTY

I know I can do this programatically using a for...next loop, but wondered if ther was a quicker/built-in way to do this?

Nelson

Hi Nelson,

It can be done but probably not as simply as a for next loop.  There's a CATS function that will concatenate to dynamic arrays  and return a new dynamic array with the results.  It's not that simple though if you want to concatenate a new delimiter.  Here's an example that does it two ways. Take a look and see which one you think is easier to understand and maintain.

      ARRAY = 'A1':@VM:'A2':@VM:'A3':@VM:'A4'
      ARRAY<2> = 'B1':@VM:'B2':@VM:'B3':@VM:'B4'

      LN = DCOUNT(ARRAY<1>, @VM)

      * PUT THEM TOGETHER WITH A FOR NEXT LOOP
      LOOP.RESULT = ''
      FOR LINE.NO = 1 TO LN
         LOOP.RESULT<1,LINE.NO> = ARRAY<1,LINE.NO>:'-':ARRAY<2,LINE.NO>
      NEXT

      PRINT LOOP.RESULT

      * DO THE SAME THING WITH THE CATS FUNCTION

      * CATS CONACTENATES THE VALUES FROM ONE ARRAY TO ANOTHER ARRAY AND RETURNS THE RESULTING ARRAY
      * IN ORDER TO USE IT, WE NEED TO HAVE A CORRECTLY SIZED ARRAY OF THE DELMITERS SO THAT WE
      * CAN CONACTENATE IT WITH THE SECOND ARRAY
      DELIMS = STR('-':@VM, LN)

      * WE'RE ALWAYS GOING TO END UP WITH AN EXTRA ELEMENT AT THE END SO WE NEED TO STRIP THAT OFF
      DELIMS = FIELD(DELIMS, @VM, 1, LN)

      * NOW WE CAN BUILD A NEW ARRAY BY CONCACTENATING OUR DELIMITERS WITH ARRAY<2>
      DELIM.ARRAY = CATS(DELIMS, ARRAY<2>)

      * FINALLY WE CAN BUILD THE NEW ARRAY WITH ARRAY<1> AND DELIM.ARRAY
      RESULT = CATS(ARRAY<1>, DELIM.ARRAY)
      
      PRINT RESULT

      STOP

If you're trying to get the best performance, CATS is faster. To loop through each of those sections 999,999 times took the first method 2,510 milliseconds. The second took 960 milliseconds.  For most real world cases it's better to go with understandable.

Hi Nelson,

It can be done but probably not as simply as a for next loop.  There's a CATS function that will concatenate to dynamic arrays  and return a new dynamic array with the results.  It's not that simple though if you want to concatenate a new delimiter.  Here's an example that does it two ways. Take a look and see which one you think is easier to understand and maintain.

      ARRAY = 'A1':@VM:'A2':@VM:'A3':@VM:'A4'
      ARRAY<2> = 'B1':@VM:'B2':@VM:'B3':@VM:'B4'

      LN = DCOUNT(ARRAY<1>, @VM)

      * PUT THEM TOGETHER WITH A FOR NEXT LOOP
      LOOP.RESULT = ''
      FOR LINE.NO = 1 TO LN
         LOOP.RESULT<1,LINE.NO> = ARRAY<1,LINE.NO>:'-':ARRAY<2,LINE.NO>
      NEXT

      PRINT LOOP.RESULT

      * DO THE SAME THING WITH THE CATS FUNCTION

      * CATS CONACTENATES THE VALUES FROM ONE ARRAY TO ANOTHER ARRAY AND RETURNS THE RESULTING ARRAY
      * IN ORDER TO USE IT, WE NEED TO HAVE A CORRECTLY SIZED ARRAY OF THE DELMITERS SO THAT WE
      * CAN CONACTENATE IT WITH THE SECOND ARRAY
      DELIMS = STR('-':@VM, LN)

      * WE'RE ALWAYS GOING TO END UP WITH AN EXTRA ELEMENT AT THE END SO WE NEED TO STRIP THAT OFF
      DELIMS = FIELD(DELIMS, @VM, 1, LN)

      * NOW WE CAN BUILD A NEW ARRAY BY CONCACTENATING OUR DELIMITERS WITH ARRAY<2>
      DELIM.ARRAY = CATS(DELIMS, ARRAY<2>)

      * FINALLY WE CAN BUILD THE NEW ARRAY WITH ARRAY<1> AND DELIM.ARRAY
      RESULT = CATS(ARRAY<1>, DELIM.ARRAY)
      
      PRINT RESULT

      STOP

If you're trying to get the best performance, CATS is faster. To loop through each of those sections 999,999 times took the first method 2,510 milliseconds. The second took 960 milliseconds.  For most real world cases it's better to go with understandable.

I have a statistics program that builds an array with <1,POS>= sku and <2,POS>=qty.  The output would often have 8-9k SKUs.

When I dump it out to a sequential file, I want to output the data as  a sinlge row for each sku in the format of SKU:<delimiter>:QTY

I know I can do this programatically using a for...next loop, but wondered if ther was a quicker/built-in way to do this?

Nelson

 I created a subroutine to work like Excel to transpose an array.  I've attached it for reference.  My first pass I used Splice but it was slow on larger data sets.  I hope this helps.

Edit:

I don't know why uploading did not seem to attach the file, so I've added it here.

SUBROUTINE TRANSPOSE.ARRAY(ITEM)
* ------------------------------------------------------------------------
*  Name        : TRANSPOSE.ARRAY
*  Description : Transpose an array
*  Programmer  : Joseph von Arx
*  Date        : 7/20/2020
*  ------------------------------------------------------------------------
*
*  Notes
*  -----
*
*  Modification History
*  Date        Who  Description
*  ----------  ---  -----------------------
*  03/20/2023  JLVA Rewrote code for optimization and efficiency for larger arrays
*
* ------------------------------------------------------------------------
    $OPTIONS DEFAULT
    FMAX = COUNT(ITEM, @AM)+1
    DIM WORK(FMAX)
    MAT WORK = ''
    MATPARSE WORK FROM ITEM USING @AM
!***
!* MAKE SURE ALL FIELDS HAVE SAME NUMBER OF MULTIVALUES
!***
    VMAX = COUNT(WORK(1), @VM)+1
    FOR FMC = 2 TO FMAX
        VMC = COUNT(WORK(FMC), @VM)+1
        IF VMC GT VMAX THEN VMAX = VMC
    NEXT FMC
!***
!* Extract the elements and load them into an array.
!* This should be faster than <> extraction on large Items.
!***
    DIM ARRAY(VMAX)
    MAT ARRAY = ''
    DIM VALARRAY(VMAX)
    FOR II = 1 TO FMAX
        LINE = WORK(II)
        MAT VALARRAY = ''
        MATPARSE VALARRAY FROM LINE USING @VM
        FOR JJ = 1 TO VMAX
            IF II # 1 THEN ARRAY(JJ) := @VM
            VAL = VALARRAY(JJ)
            ARRAY(JJ) := VAL
        NEXT JJ
    NEXT II
    MAT VALARRAY = ''
    MAT WORK = ''
!***
!* Done.
!***
    MATBUILD ITEM FROM ARRAY USING @AM
    ICNT = DCOUNT(ITEM, @AM)
    IF ICNT LT VMAX THEN
        ITEM := STR(@AM, (VMAX - ICNT))
    END
*
    RETURN
END

------------------------------
Joseph von Arx
Software Developer
Data Management Associates Inc DMA
Cincinnati OH US
------------------------------

I have a statistics program that builds an array with <1,POS>= sku and <2,POS>=qty.  The output would often have 8-9k SKUs.

When I dump it out to a sequential file, I want to output the data as  a sinlge row for each sku in the format of SKU:<delimiter>:QTY

I know I can do this programatically using a for...next loop, but wondered if ther was a quicker/built-in way to do this?

Nelson