LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Ryanjdeller

Hi there,

I've imported data in a table form from an excel spreadsheet into a node.

I have a number of values in a column and a number in another column which corresponds with a title value


Field1, Field2
1,...... Title1
........ Value2
........ Value3
........ Value4
2,...... Title 2
........ Value 5
........ Value 6
3,...... Title 3
etc.

What I'd like to do is to move the title's into a column before this column, and repeat it for each of the other values. So I'd like the output to look like this:

Field1, Field2,
Title 1, Value2
Title 1, Value3
Title 1, Value4
Title 2, Value 5
Title 2, Value 6
Title 3, Etc...

Could a while loop be used in this case?

I hope this makes sense - if you require more information, let me know!

Thanks in advance for your help!


Kind regards,

Ryan.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Elliott12

Hi Ryno,

This can be done without using a loop. To do this I used a simple IF statement as follows:

			
				dontEmit = 0


if (firstExec) then {

	CurrentTitle = "Error: First Row did not have a value in Field1"

}


if isNotNull(Field1) then {

	CurrentTitle = Field2

	dontEmit = 1

}


emit CurrentTitle as Field1, Field2

where dontEmit == 0
			

The key thing to remember is that the filter node performs the script from top-to-bottom once for each row in the input, BUT the values that you assign to variables will carry over for the next row. Does that make sense?

So all I did was use an IF statement to check if there was a value in Field1, and then assign the corresponding title value to a variable called 'CurrentTitle'.

Then use "CurrentTitle as Field1" in the emit statement.

So each row will emit with the current value of 'CurrentTitle', which will only change if the existing value in Field1 isNotNull.

I also used a boolean flag called 'dontEmi't make sure the title rows were not emitted. This gets reset to 0 at the start of processing for each row.

I also added in an extra IF statement that uses the built in variable called 'firstExec'. This varible only returns true if the row currently being processed is the first row on the input. I use this to set an initial value to 'CurrentTitle' in order to cover the case where there is no value for 'Field1' in the first row. Although this IF statement is not strictly necessary (this code will still work without this extra IF statement), it prevents the node from erroring out if it encounters bad data, and provides a more descriptive/helpful error message.

Rock out \m/

Elliott