Create a new table with columns based on row values

Hello,

I attach an image of the origin table I have. What I want to create is new table for each new “ARTCLASSID” value (the first one is EC000001). Once this table is created, I want it to have as columns the list of “FEATUREID” that EC000001 has. For example, column 1 would have as header EF007220, column 2 would have as header EF007219, etc.

Finally I have a condition that slightly changes the logic of column creation: depending on the column “FEATURETYPE”, some of these columns in the new table would have to be repeated (in the case that FEATURETYPE is “R”, then column 1 would be “EF007220 - (min)” and column 2 “EF007220 - (max)”, column 4 “EF007219 - (min)” and column 4 “EF007220 - (max)”, because both EF007220 and EF007219 have FEATURETYPE “R”. However, the next FEATUREID, EF000073, has FEATURETYPE “A”, so in this case only one column should be created, with header “EF000073”.

I attach a screenshot of the original table. From this table I would like to create one table per ARTCLASSID (so 1 table for EC000001, 1 table for EC000003, etc.) each of them with their respective columns which are taken from the values in column “FEATUREID”, and following the logic I explained regarding FEATURETYPE “R”.

Is it possible to obtain all the resulting tables using EasyMorph? If you need additional info, please let me know!

Thanks very much,

Roberto

Hi Roberto,

where do you want to create these tables? A database? A file? Somewhere else?

Hello Dmitry,

Initially, the idea would be to have one Excel file per table. So for example EC000001.xlsx with its columns, then EC000003.xlsx… and like this with all the ARTCLASSID. Once these files are created, I would like to load them in future EasyMorph projects and fill them out with data that come from other tables.

UPDATE: Here are more details: what I’m doing with this process I have outlined before is creating blank “templates” with specific row headers depending on the ARTCLASSID. The idea is to import a table into EasyMorph where there’s products and it’s associated ARTCLASSID.

Captura1

Depending on the ARTCLASSID associated to the product, another table will be created, with the template mentioned before, and in each row the products that are associated to that ARTCLASSID.

After this, what I want is to be able to fill out, from different data sources and through formulas (which I still have to see how to do), the values for the different features (columns) per product (row).

Let me know if this is clear.

Thanks!

You will need to use iterations and process groups of rows (corresponding to a table’s columns) of the original table one group after another.

For example, you can use the “Iterate table” action to iterate a module which gets a subset of rows related to one table, and pivots them into columns.

Gotchas:

  • Since the “Pivot” action sorts columns in alphabetical order, therefore in order to preserve the original column order you will need to temporarily use row numbers instead of column names.
  • The input dataset of the “Input” action is shown empty and assigned dynamically when the “Iterate table” action is executed. To populate it manually, go the “Iterate table” action (or the import action), right-click it and choose “Send to sandbox/module -> Module 1” . This will send a copy of the action’s dataset into the “Input” action of “Module 1”.

Here is an example that creates two excel files with columns as defined in Book1.xlsx. Notice that the order of columns in file “Tab 2” is reverse to “Tab 1”.
create-tables.morph (4.6 KB)
Book1.xlsx (9.7 KB)

Module "Main"
image

Module "Module 1"

1 Like

Hello Dmitry,

Thanks very much for your example. I had a couple of questions:

  • In step 2 of Module 1, I don’t understand the second step, “Filter Rows”. Why are you filtering only “Tab 2” (through a parameter)? What happens with “Tab 1”?

On the other hand, an error appears to me in the fifth action of Module 1 (Pivot), so I can’t see the whole Module 1 process with all its actions. Do you know why this could be happening? Here is a screenshot. It says that “Column [Column name] not found in the input table.”

Regards,

Roberto

The example uses iterations which in turn use modules. Iterations is an extremely powerful feature of EasyMorph, but it requires a bit of learning. I highly recommend reading these tutorial articles first:

Tutorial: Calling other modules/projects.
Tutorial: Iterations and loops.

Please make sure you understand the articles including the parts called “Advanced topics”. Watch the videos, try the examples. That will answer many of your questions or even all of them.

If something about modules and iterations in the tutorial is not clear – please don’t hesitate to ask.

Hello Dmitry,

I’ve read all the tutorials and seen the videos / examples, but I’m still having trouble understanding it. On one hand, the fact that I can’t run through all the actions of Module 1 (because there is an error in the fifth action, even though I’ve populated the table manually into this module), I can’t see the whole process.

This is happening because the Column called “Column name” has not been populated into Module 1,right? Why has it not been populated? However, I don’t understand what’s going on because even though it states there is an error here (because “Column name” is not in the input table), when I run the project as a whole, there is no error and the different excel files are created correctly. I don’t understand why in the process of Module 1, you even delete the column “Table name” (so there is no data, and “RowNo” is empty:

image

But if I run the project as a whole it still works, and I don’t understand that.

What I’ve understood is that it doesn’t matter the value you put to parameter “Table name” in Module 1, because what’s actually being used as a parameter is each unique value of “Table name” (which in this case are “Tab 1” and “Tab 2”, and two files are created because there are two rows through which the iteration passes). The fixed value you set to parameter {Table Name} in Module 1 is “Tab 2”, but this is irrelevant as far as I can see - I’ve changed this value and the results are the same. Did you set the value of parameter {Table name} to “Tab 2” for some particular reason?

I have the feeling that I’m near to understanding, something has to do “click” in my head, and I’ve gone through the whole process multiple times, but I still need something to get it…

Thanks very much,

Roberto

The “Iterate table” action has two inputs:

  1. The output of the previous action
  2. The external table it references

The action works as follows: it takes a dataset from another table (2), passes it to the called module, and runs it as many times as there are rows in its input dataset (1).

The “Input” action receives a dataset from the calling action into the called module. Because it’s called by the “Iterate table” action, it will receive the dataset of table “Book1.xlsx”. Which means that you need to right-click the last (and only) action of that table (“Import from Excel”) and choose “Send to sandbox/module -> Module 1”. Then the dataset of table “Book1.xlsx” will appear in the “Input” action of Module 1 and everything will work as expected.

It’s a bit difficult to understand at first, but once you figure it out, it’s actually pretty simple.

Hello,

Ok now it works! The problem was that I was "Sending to sandbox/module" not "Book1.xlsx" (2), but the output of the previous action (1), so I didn't send to the module "Book1.xlsx", but just the "Table 1" with the unique values of column "Table name":

Thanks a lot,

Roberto

Great! :slight_smile: