Transform Header with Dynamic name

[Harsh-Goyal]

Hi Community,

I am trying to solve the following pattern update but not success yet..

Can someone help 

[atcodedog05]

Hi @Harsh-Goyal 

Can you provide/attach the excel present in the image

[Harsh-Goyal]

Refer to the attachment below

[atcodedog05]

Hi @Harsh-Goyal 

You can try something like this

1. In Input tool set configuration that first row contains data. So that column names are set as F1, F2... (to make it easy for later mapping)

2. Using select record tool and you filter only 1st row at the the top and rest goes bottom. 1st row is the one which requires manipulation.

3. Using transpose tool to convert all columns to rows

4. Using multi-row formula tool to fill value is row+2, row+1, current row & row-1 has values. Covering 2 columns before value is present and 1 column after column present.

5. Using crosstab tool to covert rows to columns

6. Using union tool to join the rows back

7. In table tool i am deselecting Show Column headings since we dont need F1,F2... headers (In output tool also there is a option to skip field names)

8. Using render tool to output to excel

Hope this helps 🙂

[Harsh-Goyal]

Interesting solution, I thought about it but it is missing a possibility say what about those cases where Toll, FP0A row values are not fixed to only above the 3rd column, they can be present above any of the 4 number values or say in some cases it might even be filled above those 4 number values..

refer to snipper below..

Just trying to cover all the possibilities here...😅

I was able to build logic but its divided into multiple phases and a little longer one..and yes I am trying to solve additional problems here though.. the above ask was a subset of the broader issue..😉

Input Data Structure:                                                                   Workflow:

Output Structure:

Steps:

1. First, select the top 2 rows with the total possible number of columns and assign them a unique id based on the common column for each set of the company (Toll, etc..)

2. Then giving a unique code to each company in order of their occurrence (Same order)

3. Link back the company name based on ID to #1

4. Link is back to an original dataset with the same set of column header now with cleaned-up info. 

5. Finally transpose it back..

Additionally, if you are working over it, please send the yxmd file as I have an older version and your one is the latest so yxzp is not opening..

[atcodedog05]

Hi @Harsh-Goyal 

I agree your scenario seems to be much more complicated 😅

Here 2 post which will help you in downgrading.

How to downgrade packaged workflow (File types: .yxzp) 

How to downgrade .yxmd(Workflow), .yxmc(Macro), .yxwz(Analytic app) file types 

[Harsh-Goyal]

But thanks for spending time and contributing to it..

[atcodedog05]

Hi @Harsh-Goyal 

If you are looking for vertical based output structure it is much more easier. I can work on it and let you know.

[Harsh-Goyal]

Agreed i use to fix it using Notepad but as soon as I opened it in Notepad++ its not opening up as XML 

[atcodedog05]

workflow attached