Creating as many rows as the value of one cell

Question

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?

ron_horne · Answer

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

ian_jmp · Answer

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: * Replace the code after '//(1):' and before '//(2)' with the single line: dt1 = CurrentDataTable(); * 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]));

saitcopuroglu · Answer

Thanks Ian,

For my beginner skills it is a rocket science.