Hello,
I’ve been able to do a certain transformation in EasyMorph, but the process takes around 1 hour, and I’m looking for a way to make it faster. As I describe below, I think that there’s one key aspect that is inefficient and takes a lot of resources, but for now it’s the only way I’ve been able to achieve the transformation.
Basically, what I want to do is the following: in the following sample origin data, there are different product features, each of them in 1 line. For example, product 0003006816 has 16 features, and then below come the features of the next product. Each feature has a “Feature description”, a “Value” and a “Unit of measure” (the last three columns). The values of the three columns before these, “Feature”, “Feature value” and “Feature unit of measure” are going to be the headers of the different columns once this table has been pivoted three times, because the idea is to have only 1 row per product, with all its relevant information in that row.
So here is a screenshot of the sample origin data:
And here is a screenshot of the result table after the transformation:
I attach the project here and the sample data:
Triple pivot.morph (10.7 KB)
Sample data.xlsx (14.3 KB)
In tab 1 of the project, 99% of the execution time is spent in table “Keep only 1 row per code”. Here, there are two iterations (one “iterate table” (Module 1), and an “iterate column” (Module 2) nested inside this one) that transform multiple rows into 1 row still keeping all the relevant information per product. The action I do inside the “iterate column” module is a “fill down” action that seems pretty inefficient, but for now it’s the only way I’ve come up to solve this.
In this example there are 10 different codes, and the 10 iterations to obtain the desired result takes around 14 seconds in my computer. This is just a sample and I will regularly do it with around 2200 codes or more. With 2200 codes its approximately 50-60 minutes, and I wanted to see if I can reduce this execution time.
NOTE: If instead of using the “iterate column” action I just use the “fill down” action in selected columns, it goes much faster (as it doesn’t have to iterate each column), however this doesn’t work for me because the number of features varies from product to product - in this example, the maximum number of features per product is 16. So for this case, if I used the “fill down” action selecting each column individually, I could get until the fields of feature 16 - however if in the future there is a product with 18 features, the “fill down” action will only be configured to work until the column of feature 16, so the columns of feature 17 and 18 will not get filled down. That’s why I used the “iterate column” action, so the “fill down” action takes place on all columns (except column “Code”) independently of how the column is called.
Thanks very much,
Roberto