Choose Language Hide Translation Bar
Community Trekker

Creating as many rows as the value of one cell

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;

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

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 4
Staff

Re: Creating as many rows as the value of one cell

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))),

);

// (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]));

Community Trekker

Re: Creating as many rows as the value of one cell

Thanks Ian,

For my beginner skills it is a rocket science.

Staff

Re: Creating as many rows as the value of one cell

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))),

);

// (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]));

Super User

Re: Creating as many rows as the value of one cell

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",

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] )

),

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 );

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 );