Subscribe Bookmark RSS Feed

Need Inputs on Transforming a Table or Matrix

csoon1

Community Trekker

Joined:

Feb 9, 2016

This question is related or a follow-up to my previous post (Need suggestion on best way to add table column for an ID that does this....). On that post, my question was specifically answered and there were lots of tips I learned. However, I am again in need of a good suggestion on how to manipulate a table or matrix to transform it from the original form to the intended form.

Assuming this is my original table (or matrix of similar form):

Table 1: Original Table

Column AColumn BColumn CColumn D
aeim
bfjn
cgko
dhlp

I wanted each row from this table (or matrix) to be repeated "n" times such that the resulting table (or matrix) would look like this:

Table 2: Intended New Table Format

Column AColumn BColumn CColumn D
aeim
aeim
aeim
aeim
bfjn
bfjn
bfjn
bfjn
cgko
cgko
cgko
cgko
dhlp
dhlp
dhlp
dhlp

In the example above, each row from the original table was repeated "4" times.

As I was playing with "shaping" a matrix and applying the suggestion of concatenating tables "n" times from my previous post, I was able to create this table (from my original table)

Table 3: Some manipulated form of Table 1 done by Shaping the Matrix and Repeating the rows "n" times

Column AColumn BColumn CColumn DColumn EColumn FColumn GColumn HColumn IColumn JColumn KColumn LColumn MColumn NColumn OColumn P
aeimbfjncgkodhlp
aeimbfjncgkodhlp
aeimbfjncgkodhlp
aeimbfjncgkodhlp

However, I am stuck here. I would have wanted this table to look like my intended table (Table 2) as described above. I appreciate any comments and suggestions. I believe there are more intuitive and efficient ways of handling this than what I have in mind. Thanks.

3 REPLIES
csoon1

Community Trekker

Joined:

Feb 9, 2016

I just thought of something now... I think it's possible to simply clone the original table "n" times... and then just sort the rows according to a certain column (let's say Column A). Do you think this works?

ian_jmp

Staff

Joined:

Jun 23, 2011

That looks like it will give you 'Table 2' above. In JSL (if 'dt' is a reference to your table):

dt << Sort(By(:Column A));

txnelson

Super User

Joined:

Jun 22, 2012

Adding to Ian's response:

The cloning of the table can be done in one step.

Names Default to Here(1);

dt= current data table();

dt << Concatenate(dt,dt,dt,append to first table(1));

dt << Sort(By(Column A));

In many cases where I have used this technique, there does not exist a column to sort on to get the final order.  So what I do in that case is to create such a column

Names Default to Here(1);

dt= current data table();

dt<<new column("rownum",values(1::N Rows(dt));

dt << Concatenate(dt,dt,dt,append to first table(1));

dt << Sort(By(Rownum));

dt<<delete columns("rownum");

Jim