Transform data

[Markus3003]

Hi all,

I have now the following issue, where I do not know excactly how to start: Hope that someone can give me some advice and which nodes to use: Please check my Scrennshot

Trying to describe it step by step:

1. Filter Column Q from the input file on “00444Q”
2. Take that data (marked green) and move the data like shown in the screenshot into an Output file:

• Values of Column H of Input File → into Column H “WRBTR” of Output File
• Values of Column J of Input File → into Column J “KOSTL” of Output File
• Values of Column N of Input File → into Column G “KONTO” of Output File
• Values of Column W of Input File → into Column L “SGTXT” of Output File
• Values of Column AH of Input file → into Column K “ZUONR” of Output File

3. Also add in the Output file the Columns A-F, I and M like in the Screenshot with:

• Column A-C with the current Date
• Column D, E, F, I are always the same values.
• Column M is empty

4. After all that probably the more difficult part begins, I would like to add one row (orange in the Screenshot) that row should show:

• In Column A-C the current date like in the Screenshot
• in Column D-G the fixed values like in the Screenshot
• in Column H the sum from all the values of column H with “-”
• in Column I- K the fixed values like in the Screenshot
• in Column L the Text with current date
• Column M empty.

I know thats a lot but I would highly appreciate any help!

Thank you and Best,
Markus

[takbb]

Hi @Markus3003 ,

That’s quite a lot of specification

The biggest struggle I had was creating some test data based on your sample. Maybe uploading the sample of the data you have in your screenshot, with sensitive items removed would have been a good approach to this.

I have quite literally followed the instructions you have listed and produced a workflow. Some of the data I wasn’t sure of (e.g. you had obfuscated the KOSTL value form the orange row, but then said the value should be as per the screenshot… so I just made up a value?)

Anyway, take a look at the attached workflow. I’ve tried to mark the approximate steps from your post so you can see roughly where things are done.

It isn’t that pretty and fairly manual! Maybe instead of using a series of “Constant Value Column” nodes, I should have just used a Table Creator to provide all the constant values, but it comes out much the same way and hopefully the workflow is relatively easy to understand.

If I got confused anywhere or made a silly mistake then apologies. I did find myself struggling in working the column names as letters versus actual names, but I tried to match your written spec.

If there are refinements needed, or alternative ways of doing things then at least it gives you, me, or somebody else on the forum something to work with
20210907 - Transform Data.knwf (54.7 KB)

(Incidentally, the “Cross Joiner” node wasn’t strictly necessary. I could have applied each of the “constant” nodes from Step 3 in line after the sample data was read. I only did it this way as I’d already put together that part, which involved the Create Date&Time Range node as a “starting point” and so it just flowed better in my head to do it this way )

[Markus3003]

Thanks a lot @takbb that looks pretty amazing! I will try it like that.

[Daniel_Weikert]

Kudos for this detailed work without sample data!