topic Re: Map values from single column to multiple columns where column name is in the same cell as the value (see screenshots) in Data Quality

Map values from single column to multiple columns where column name is in the same cell as the value (see screenshots)

Anonymous — Sat, 16 Nov 2024 06:18:18 GMT

I have a spreadsheet that is only one column. Each row has the name of a field and a value. I need to get each set of rows, which has a variable number per set, into a single row. Any ideas how I can accomplish this?

It should come out like this:

Thanks!

Re: Map values from single column to multiple columns where column name is in the same cell as the value (see screenshots)

vapukov — Tue, 19 Mar 2019 00:24:28 GMT

Hi,

with this type of question - you never will have final advice. Too many IF, and too many behind the scene 🙂

but some ideas, please find below.

Idea based on provided by you data, which:

have implicit groups of rows
have standard separator between groups

in this case you could use next components:

tFixedFlowInput - you could replace to your own input component, with a single column in the schema (in my example it named "data")

tJavaFlex - assign group number based on default separator "--------"

tFilterRow - reject empty and separator rows from the flow

tExtractDelimitedFields - split column data into 2 columns by separator

and finally, tPivotToColumnDelimited transform output result

output result:

you could addition clean/remove column grp from output file

one more time - it is just an idea, final job will always depend from you real data and other restrictions

Re: Map values from single column to multiple columns where column name is in the same cell as the value (see screenshots)

akumar2301 — Tue, 19 Mar 2019 10:34:47 GMT

There is a workaround , but it is by converting your file to LDIF file .

you need read your excel and convert it like this ( dn : linenumber with previous line blank is mandatory)

dn:1
Field1:1
Field2:2

dn:2
Field1:3
Field2:4

dn:3
Field1:4

the use tFileInputLdif and tlogrow row. Output will be like this

https://help.talend.com/reader/KxVIhxtXBBFymmkkWJ~O4Q/eRexaC1aghU1uSFx2gLqMQ