Subscribe Bookmark RSS Feed

Creating as many rows as the value of one cell

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

I really did not how to categorise this question;

I pull down the data from an SQL Server by excel, and as a logic of SQL for every occasion a row is created. In this case the occasion is accomodation and it is measured by "RoomNight". Therefore "RoomNight" shoul consists of the value of  "1", if the accomodation is for 300 nights it means that reservation should have 300 rows;

8416_Screen Shot 2015-03-24 at 12.08.55.png

However, our reservation administrator is sometimes making a mistake and the result for 50 night accomodation looks as follows;

8420_Screen Shot 2015-03-24 at 12.07.57.png

What I have to do is creating 50 rows for each 50 value cell with the value of "1" for "RoomNight" and "2" for AdultNight".

Is it possible?

4 REPLIES
ian_jmp

Staff

Joined:

Jun 23, 2011

This old code nearly does what you want, and could be adapted to your special case fairly easily if you know a little JSL.

NamesDefaultToHere(1);

// Given a row ve3ctor 'inVec', forms a matrix composed by vertically concatenating inVec with itself 'inVec[1, pos]'

// times to give a matrix 'outMat'. Column 'pos' in 'outMat' consists of 1's.

extrudeRow =

Function({inVec, pos}, {Default Local},

  if(pos > 0,

  // Is 'pos' a valid location?

  times = Round(inVec[1, pos], 0);

  if(times > 0,

  // Extrude the vector

  outMat = J(times, NCol(inVec), .);

  for(r=1, r<=times, r++,

  outMat[r, 0] = inVec;

  );

  outMat[0, pos] = 1,

  // Else do nothing

  outMat = inVec;

  ),

  //Else do nothing

  outMat = inVec;

  );

  // Return the result

  outMat;

);

// Uses 'extrudeRow()' to extrude a whole matrix by vertical concatenation

extrudeTable =

Function({inMat, pos}, {Default Local},

  if(pos > 0,

  // Extrude the matrix

  newRows = sum(inMat[0, pos]);

  outMat = J(newRows, NCol(inMat), .);

  rStart = 1;

  for(r=1, r<=NRow(inMat), r++,

  outMat[rStart::(rStart+inMat[r, pos] - 1), 0] = extrudeRow(inMat[r, 0], pos);

  rStart = rStart + inMat[r, pos];

  ),

  // Else do nothing

  outMat = inMat;

  );

  // Return the result

  outMat;

);

// Show the use of 'extrudeTable()' . . .

// (1): Make a table to play with

dt1 = NewTable("Data",

  NewColumn("Column 1", Numeric, Ordinal, Formula(RandomInteger(-5, 5))),

  NewColumn("Column 2", Numeric, Ordinal, Formula(RandomInteger(-5, 5))),

  NewColumn("Frequency", Numeric, Ordinal, Formula(RandomInteger(1, 5))),

  AddRows(5)

  );

// (2): Get the data as a matrix

mat1 = dt1 << getAsMatrix;

// (3): Extrude this matrix using the 'Frequency' (the third) column

mat2 = extrudeTable(mat1, 3);

// (4): Make a table from mat2 and fix up the column names so they match dt1

dt2 = AsTable(mat2);

dt2 << setName((dt1 << getName) || " Extruded by 'Frequency'");

cols = dt1 << getColumnNames("String");

for(c=1, c<=NItems(cols), c++, Column(dt2, c) << setName(cols[c]));

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

Thanks Ian,

For my beginner skills it is a rocket science.

ian_jmp

Staff

Joined:

Jun 23, 2011

Like all programming, it's actually just a series of steps.

But of course you don't have to (fully) understand something to be able to use it effectively, so I've made the changes to do what I think you want.

Do 'File > New > New Script' to open an editor window, and cut and paste the code below into this. Then do 'Edit > Run Script' to check that I did understand correctly using the simple example provided (which will be different each time you run it)..

If you want to run it with your own data, make sure this table is active. Then modify the code below as follows:

  1. Replace the code after '//(1):' and before '//(2)' with the single line: dt1 = CurrentDataTable();
  2. Change the '3' and the '4' in the line immediately after '//(3)' to reflect the positions of the columns in your own table.

This code only works if you table contains numeric columns only (as yours appears to).

if this is something you (or someone else) wants to do routinely, it would be just a little work to make an add-in to hide this complexity.

And I'd be very much interested in solutions to this that others might offer. As the saying in English goes, 'there are many ways to skin a cat' . . .

NamesDefaultToHere(1);

// Given a row ve3ctor 'inVec', forms a matrix composed by vertically concatenating inVec with itself 'inVec[1, pos]'

// times to give a matrix 'outMat'. Column 'pos' in 'outMat' consists of 1's. Column 'numPos' in outMat will contain

// the value of 'inVec[1, numPos]' split equally between the rows

extrudeRow =

Function({inVec, pos, numPos}, {Default Local},

  if(pos > 0,

  // Is 'pos' a valid location?

  times = Round(inVec[1, pos], 0);

  if(times > 0,

  // Extrude the vector

  outMat = J(times, NCol(inVec), .);

  for(r=1, r<=times, r++,

  outMat[r, 0] = inVec;

  );

  outMat[0, pos] = 1;

  outMat[0, numPos] = inVec[1, numPos] / times,

  // Else do nothing

  outMat = inVec;

  ),

  //Else do nothing

  outMat = inVec;

  );

  // Return the result

  outMat;

);

// Uses 'extrudeRow()' to extrude a whole matrix by vertical concatenation

extrudeTable =

Function({inMat, pos, numPos}, {Default Local},

  if(pos > 0,

  // Extrude the matrix

  newRows = sum(inMat[0, pos]);

  outMat = J(newRows, NCol(inMat), .);

  rStart = 1;

  for(r=1, r<=NRow(inMat), r++,

  outMat[rStart::(rStart+inMat[r, pos] - 1), 0] = extrudeRow(inMat[r, 0], pos, numPos);

  rStart = rStart + inMat[r, pos];

  ),

  // Else do nothing

  outMat = inMat;

  );

  // Return the result

  outMat;

);

// Show the use of 'extrudeTable()' . . .

// (1): Make a table to play with

dt1 = NewTable("Data",

  NewColumn("Column 1", Numeric, Ordinal, Formula(RandomInteger(-5, 5))),

  NewColumn("Column 2", Numeric, Ordinal, Formula(RandomInteger(-5, 5))),

  NewColumn("Frequency", Numeric, Ordinal, Formula(RandomInteger(1, 5))),

  NewColumn("Number", Numeric, Ordinal, Formula(RandomInteger(1, 20))),

  AddRows(5)

  );

// (2): Get the data as a matrix

mat1 = dt1 << getAsMatrix;

// (3): Extrude this matrix using 'Frequency' (the third) column and average the values of 'Count' (the fourth) column

mat2 = extrudeTable(mat1, 3, 4);

// (4): Make a table from mat2 and fix up the column names so they match dt1

dt2 = AsTable(mat2);

dt2 << setName((dt1 << getName) || " Extruded by 'Frequency'");

cols = dt1 << getColumnNames("String");

for(c=1, c<=NItems(cols), c++, Column(dt2, c) << setName(cols[c]));

ron_horne

Super User

Joined:

Jun 23, 2011

try the following, it is based on bubble plots - unequal time intervals

this will multiply only the nights that are higher than 1.

New Table( "RoomNight",

    Add Rows( 15 ),

    New Column( "Original row order",

        Numeric,

        Continuous,

        Format( "Best", 12 ),

        Set Values( [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15] )

    ),

    New Column( "RoomNight",

        Numeric,

        Continuous,

        Format( "Best", 12 ),

        Set Values( [1, 5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 60, 65, 70] )

    ),

    New Column( "AdultNight",

        Numeric,

        Continuous,

        Format( "Best", 12 ),

        Set Values( [2, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130, 140] )

    )

);

// record the number of nights and adults before doing anything

total nights pre = Col Sum( :RoomNight );

total adult nights pre = Col Sum( :AdultNight );

dt = Current Data Table();

rowN = N Row();

// For each original row, produce new rows as many as the duration of each event.

For( i = 1, i <= rowN, i++,

    rowi = dt << get row( i );

    For( z = 1, z <= (:Name( "RoomNight" ) - 1), z++,

        dt << add row( rowi );

        dt << run formulas;

    );

);

// Sort the observations to their original order

Data Table ("RoomNight") << Sort( By( :Original Row order ), Order( Ascending ), replace table );

// over ride the wrong number of nights

:RoomNight << Set Each Value( 1 );

:AdultNight << Set Each Value( 2 );

Wait( 0 );

// record the total number of nights in the new table

total nights post = Col Sum( :RoomNight );

total adult nights post = Col Sum( :AdultNight );

// see that all totals didn't change

Show( total nights pre, total adult nights pre, total nights post, total adult nights post );