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]

I'm afraid the description does not give enough information. Maybe if you show a "before" and "after" table, it might fill in the blanks

[Anonymous]

Hi rhall_2_0.

sure,

At the beginning, I have 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

After the pivot, i get this table:

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

and now, I want to obtain this one:

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

[Anonymous]

There are a couple of ways in which you could do this, but since you have started with the pivot table way, I'll go with that. I am making the following assumptions....

1) You know that there will always be codes A to E (or at least be aware of a maximum number of codes).

2) The rest of your data is pretty static (as shown in the examples)

3) That you can change your pivot table calculations (you are currently using "id" where I think you should be using "value")

If you can get a pivot table like below....

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

....you can use a tAggregatedRow, group by "id" and output the rest of the columns using the "First" function while ticking "Ignore null values". This should return 1 row .....but you might need to tweak what I have described with a little experimentation

[Anonymous]

Hi,

I took your advises in account for the pivot table and it's OK but I have still an issue with the tAggregatedRow.

For the point 2) & 3) of your assumptions, you are right but for the point 1), it is not the case. I have provided a short list (A to E) for the example but this list is really longer and all contained values could be not present in all run. 

Probably is because I'm a newbie in Talend and do not know how to well managed it but with the tAggregatedRow, I get only columns of my first table(id, code, value, day, time, utc). I saw that I can add manually the others (A to E) by editing the schema but I'm not sure is the correct way due to the previous explanation. I can export my project if it can help you to understand my issue.

regards

[Anonymous]

OK, do you know of a maximum number of dynamic columns you might get?

[Anonymous]

Sure, the maximum of columns is 1500.

I'm currently looking about dynamical schema but it's look only within the enterprise version of Talend with subscription. 

[Anonymous]

Wow! OK, I think I am misunderstanding your requirement now. You surely cannot be wanting to produce a single row with up to 1500 columns in it? 

[Anonymous]

Yes I want. it's the reason why i try to do this transformation. I want one line  with all value by code for one id at one moment (day,time,utc).

[Anonymous]

OK. We can do this, but I will need to know more. How does it need to be represented when it is done? Is it in a CSV file or is it for a databse. If it is for a database, my next question is do you have a table already configured to receive that data?