tNormalize on 2 columns (or more)

[Anonymous]

Hello,
is there a way to Normalize a row on 2 columns ?
index;col1;col2
row1;blue,green;1,2
row2;black,white,red;3,4,5
would become
row1;blue;1
row2;green;2
row3;black;3
row4;white;4
row5;red;5

regards
Xavier

[Anonymous]

Hello Xavier
You need to write some java code to finish your request. Here is my example:
Go to Repository-->Code-->and click on the Routine, select 'create routine' option, create a new routine:

package routines;
public class Forum6748Routine {
	static java.util.Map<String, String> map = new java.util.HashMap<String, String>();
	public static String getCol2Value(String col2) {
		String value = null;
		String[] array = col2.split(",");
		for (int i = 0; i < array.length; i++) {
			String item = array;
			if ((String) map.get(item) == null) {
				value = item;
				map.put(item, item);
				break;
			}
		}
		return value;
	}
}

test.csv:

row1;blue,green;1,2
row2;black,white,red;3,4,5
row3;butter,charcoal,clay,yellow;6,7,8,9

result:

Starting job forum6748 at 20:25 25/05/2009.
.-----+--------+----.
|     tLogRow_1     |
|=----+--------+---=|
|rowID|col1    |col2|
|=----+--------+---=|
|row1 |blue    |1   |
|row2 |green   |2   |
|row3 |black   |3   |
|row4 |white   |4   |
|row5 |red     |5   |
|row6 |butter  |6   |
|row7 |charcoal|7   |
|row8 |clay    |8   |
|row9 |yellow  |9   |
'-----+--------+----'
Job forum6748 ended at 20:25 25/05/2009. 

Let me know if you have any questions!
Best regards

shong

[Anonymous]

OK.
I use a different function but I'm using you method with 2 others static variables.
Thanks for this idea.

[Anonymous]

Hi Shong,
Do i just duplicate the above Java Routines with different Column Name and i shall be able to Normalize more than 2 columns?
I've tried to amend the routine but it doesn't give me my expected result.
public static String getDEBIT_MOVEMENTValue(String DEBIT_MOVEMENT) {
String value = null;
String[] array = DEBIT_MOVEMENT.split(",");
for (int i = 0; i < array.length; i++) {
String item = array ;
if ((String) map.get(item) == null) {
value = item;
map.put(item, item);
break;
}
}
return value;
}
My Data:
@ID;K_TYPE;BALANCE;DEBIT_MOVEMENT;CREDIT_MOVEMENT;LOCAL_BALANCE;LOCAL_DEBIT_MVE;LOCAL_CREDT_MVE
LD.1.TR.USD.21052.2001.MY.5Y....2800.....MY0012101;51001,LIVEDB;-1240.91,-1000000;-221.53,-1700000;,; -3802.83,-3064550;-678.24,-5209735;3.66,7965

My Output:
+---------------+-------------+---------------+--------------=|
|_ID |K_TYPE|BALANCE |DEBIT_MOVEMENT|CREDIT_MOVEMENT|LOCAL_BALANCE|LOCAL_DEBIT_MVE|LOCAL_CREDT_MVE|
|=-------------------------------------------------+------+--------+--------------+---------------+-------------+---------------+--------------=|
|LD.1.TR.USD.21052.2001.MY.5Y....2800.....MY0012101|51001 |-1240.91|-221.53 |null |-3802.83 |-678.24 |3.66 |
|LD.1.TR.USD.21052.2001.MY.5Y....2800.....MY0012101|LIVEDB|-1000000|null |null |-3064550 |null |null |
'--------------------------------------------------+------+--------+--------------+---------------+-------------+---------------+---------------'
The K_TYPE, BALANCE and LOCAL_BALANCE is Delivery the expected result. But i do not know why the other 3 columns does not delivery as per expected results.
Pleas Help!
Thank you!

[Anonymous]

Hello All,
Can anyone please help me to resolve the above issue please~~~
Thank You!

[Anonymous]

Hi ectl_mun
Can you give us an example to explain your request? What's the input data? What are your expected result?
Best regards
Shong

[Anonymous]

Hi Shong,
I have a ; delimited file with 7 columns, and 6 of the columns is having multivalue delimited by | , the multilvalue column, might have up to 4 values.
My Data:
@ID; K_TYPE; BALANCE; DEBIT_MOVEMENT; CREDIT_MOVEMENT; LOCAL_BALANCE; LOCAL_DEBIT_MVE;
MY0012101; 51001|LIVEDB; -1240.91|-1000000; -221.53|-1700000; |; -3802.83|-3064550; 678.24|-5209735;
My expected result is:
@ID; K_TYPE; BALANCE; DEBIT_MOVEMENT; CREDIT_MOVEMENT; LOCAL_BALANCE; LOCAL_DEBIT_MVE;
MY0012101 51001 -1240.91 -221.53 null -3802.83 -678.24
MY0012101 LIVEDB -1000000 -17000000 null -3064550 -5209735
Please help me!!
Thank You!

[Anonymous]

Hi Shong,
Any idea on how i can resolved this issues?
Please help!
Thank You!

[Anonymous]

Hello Xavier
You need to write some java code to finish your request. Here is my example:
Go to Repository-->Code-->and click on the Routine, select 'create routine' option, create a new routine:

package routines;
public class Forum6748Routine {
	static java.util.Map<String, String> map = new java.util.HashMap<String, String>();
	public static String getCol2Value(String col2) {
		String value = null;
		String[] array = col2.split(",");
		for (int i = 0; i < array.length; i++) {
			String item = array;
			if ((String) map.get(item) == null) {
				value = item;
				map.put(item, item);
				break;
			}
		}
		return value;
	}
}

test.csv:

row1;blue,green;1,2
row2;black,white,red;3,4,5
row3;butter,charcoal,clay,yellow;6,7,8,9

result:

Starting job forum6748 at 20:25 25/05/2009.
.-----+--------+----.
|     tLogRow_1     |
|=----+--------+---=|
|rowID|col1    |col2|
|=----+--------+---=|
|row1 |blue    |1   |
|row2 |green   |2   |
|row3 |black   |3   |
|row4 |white   |4   |
|row5 |red     |5   |
|row6 |butter  |6   |
|row7 |charcoal|7   |
|row8 |clay    |8   |
|row9 |yellow  |9   |
'-----+--------+----'
Job forum6748 ended at 20:25 25/05/2009. 

Let me know if you have any questions!
Best regards

         shong

Hi Shong,
Can you advice more detail how to do this? Should I apply that function with tMap or just use normalize.?
Thank you and Best Regards,

[Anonymous]

Hello narutodc
This topic was a little old, in order to follow up your problem well, please report a new topic for your question.
Thanks!
Shong