How to replace a value in multiple columns aftere a pivotToColumnsDelimited

[Anonymous]

Hello everybody,

I need some help and hope you will manage to do it. I Have the following data get after a pivotToColumnsDelimited. I need to replace the value in each column A, B,C, etc... where the id is set by the corresponding value in the column Value. I tried with a tReplace, a tMap but i do not know how to do it. I precised that the columns A,B,etc... are created by the pivot done in the last step of my current process. If you have some idea. Thanks 

id	value	day	time	utc	A	B	C	D	E
28775	55	0	15:00:00	2	28775
28775	68.3	0	15:00:00	2		28775
28775	95	0	15:00:00	2			28775
28775	224	0	15:00:00	2				28775
28775	135	0	15:00:00	2					28775

[Anonymous]

No it's only csv file but I manage to create an tFileInputDeliminted by using metadata menu. Like that i managed to create a dynamic schema and know i will retest your first idea

[Anonymous]

OK, I have a better idea that will be far more efficient.

1) You start with this table.....

id	code	value	day	time	utc
28775	A	55	0	15:00:00	2
28775	B	68.3	0	15:00:00	2
28775	C	95	0	15:00:00	2
28775	D	224	0	15:00:00	2
28775	E	135	0	15:00:00	2

Read from that in the same way you are doing it now. You should have 5 columns (id, code, value, day, time, utc). I am assuming you are OK with the data types. But since you are just intending to output these as Strings (all CSV fields are essentially Stinrg) it makes sense to just read them all as Strings.

2) Now for this part it is important that you understand that the assumption is that  id, day, time and utc will not change in the one dataset.

Connect to a tJavaFlex.id = ""

In the Start Code section, use the following code.....

String id = "";
String values = "";
String day = "";
String time = "";
String utc = "";

In the Main Code section, use the following code....

id = row1.id;
day = row1.day;
time = row1.time;
utc = row1.utc;
values = values+","+row1.value;

In the End Code section, use the following code....

globalMap.put("id", id);
globalMap.put("day", day);
globalMap.put("time", time);
globalMap.put("utc", utc);
globalMap.put("values", values);

3) Now create a new SubJob starting with a tFixedFlowInput.

Create a column for each of the variables above (id, day, time, utc, values).

In the corresponding column, add the following code (change the values to suit the column)....

((String)globalMap.get("id"))

4) Connect that to a tMap. Create 1 output column called "content".

In that column concatenate all of the columns shown above (I will assume the row is "row4")....

row4.id+","+row4.day+","+row4.time+","+row4.utc+row4.values

Notice in the above I have put a "," between each of the columns but not between the row4.utc and row4.values. That is because the row4.values column will always start with a ",".

5) Now connect to a tFileOutputRaw component. This will output to your file. You are manually handling the CSV formatting and just outputting that to a single column. 

If you look at your file and assuming that you have done everything correctly (and I have not made any significant errors....this is without having tried it), you should see what you want

[Anonymous · Accepted solution]

Hi rhall_2_0,

sorry for the delay. I finally did by a full java code. It was easier to do it. In all cases thanks for your help.

best regards