conversion of data from Alteryx to KNIME

Hi Team,

Please help me in converting the data from Alteryx to KNIME.

The present data in knime is as below

Please let me know for further information.

Thanks,
Subbu

I want to convert it as below
image

Looks like the captions are not constant or missing in the source. Can you please show the raw data source, e.g. the CSV-file or whatever you are trying to read in?

Hi @McReady

please find the attached data source
data.xlsx (8.5 KB)

let me know for further information.

@Subramanyam Yes, this is a very special format …
Column A works as intented but the rest looks like someone tried to apply any “normal form” on it and messed up the whole thing.
Please try to read in the sheet without column labels in the first step (you get names like Column A, B, C, etc., don’t care; in Excel Reader).
Split the first column from the rest (Column Splitter).
Split the first row from the data (Row Splitter).
You have to transform the “headers” of columns B till end to the desired date format with year (string manipulation node: “join(“2024-”,value”). Don’t care it is not a date-time-type yet, you can transform it later if needed.
Concatente the rows again and check the RowIDs are not altered in Concatenate node. Add the CVS Item column again to the data (Column Appender node, CVS first, data second).
Now you should be able to apply “Row to Column Names” node.
Next start a cunk loop with size 1 (default).
Column Splitter → CVS on top, data to bottom and continue with the data only:
Use the Transposer node to turn your data by 90 degree.
Use RowID to extract the date from the row id to data (Extract RowIDs), uncheck “Replace”!
Rename the new columns as desired
Column Appender again with the upper port (CVS) now.
Apply “Missing Value” node and fill up CVS.
End loop
Rename and resort columns as desired.

Hi @McReady

I have tried transposer node from bottom in the above provided list of steps and unable to fetch the data from next step.
Could you please help me in it. attached my workflow as well.

Datamodel.knwf (114.2 KB)

Hi team, @McReady @iCFO,

I have tried the above suggested steps but it is giving only the result for one date
Could you please help me in it. attached my workflow as well.

Datamodel.knwf (180.6 KB)

@Subramanyam First of all: Congratulations, great you made it with my text.

Easy one :slight_smile: Add a “RowID” node directly after the “Chunk Loop Start”. Configuration: “Replace RowIDs, Generate New” (default). Little cheat, so that the column name after Transposing is always “Row0” and you do not have to move the content of over 100 columns to the left. :wink:
After the “Loop End”, you rename “Row0” to “Vulnerability” and you have the desired format that looks like in your initial posting.
I already put both into the workflow attached. If it fits, please mark as “Solution” to help others, too. Thanks! Otherwise feel free to ask again

Datamodel v1.01.knwf (58.7 KB)

2 Likes

Why not use the Unpivot node?
It does exaclty what you seek, or am I missing something?

1 Like

Thanks a lot I achieved it.

2 Likes

Did you use the Unpivot node? That would definitely have been my approach as well.

I have tried that but it didnt worked for me

1 Like

datamodel unpivot.knwf (259.2 KB)

In case you are interested in the unpivot implementation or improved efficiency.

3 Likes

Thanks. First time I see this node making sense to me. :+1:

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.