1. Senior Member
Join Date
Nov 2013
Posts
382

## Unusual transform

This is probably the most stupid format I have ever seen, but unfortunately it's beyond our control. I have been playing with normalize/denormalize but unable to find a suitable way to do it.

We receive rows with some non-zero values and we should produce rows with pairs of fields, one with a fixed name for every non-zero value and a second with the non-zero value itself ... and of course (this is the real problem) the non-zero pairs on the firsts fields of the row

Input rows:

id,V1,V2,V3,....,V20

where some Vi are not zero.

Output rows:

id,'F3',V3,'F5',V5,null,0,..... (for a row with v3 and v5<>0)
id,'F1',V1,'F7',V7,'F19',V19,null,0,... (for a row with v1, v7 and v19 <>0)

For example:

1,0,0,25,0,30,0,...,0
2,100,0,0,0,0,0,70,0,...,115,0

must become

1,'F3',25,'F5',30,null,0,...
1,'F1',100,'F7',70,'F19',115,null,0,...

Any idea?

Thks

2. Senior Member
Join Date
Jun 2012
Posts
5,534
You have some CSV file with an id and a certain number (n) of Integer fields.
You want to have a CSV file with all the non-zero fields shifted to the left.
Also, you want to add the respective fieldnames in front of their values?
I have no idea.

3. Junior Member
Join Date
Aug 2016
Posts
11
Hardly ideal but you can do it with arbitrary javascript. Or a bunch of filters and 'set values' in a row.

Do you at least know beforehand how many incoming fields you have and what are their respective names?

4. Junior Member
Join Date
Feb 2014
Posts
5
A full solution will require some moire analysis, but I would probably attempt to tackle this problem by:
* using the "Row Normalizer" step to covert the columns of your input rows into seperate rows
* add the names as columns to these new rows
* sort the rows as required, using temporary columns if required
* flatten the rows using the "Row flattener" or "row denormalizer" step

Do your rows always have 20 columns, or can this vary?

5. Senior Member
Join Date
Nov 2013
Posts
382
Thks marabu, adding a splitfield step (I need to create an sql row!!) to your example worked as expected. I tried the group solution but was "obsessed" with getting fields so didn't think about creating a line and splitting it later!

sven, pedro: yes it's a fixed number of fields.

PS. I guess the one who designed this format doesn't even know what the words normal and form mean

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•