Hi,
Is there any transformation to deal with joined cells?
What do you recommend to transform 2 sheets like that in a database format? i.e.
Date; Division; Actual value ; Target Value;
or
Date; Division; Actual /Target; Value;
Best Regards,
Michel
The proposed solution is not very straight forward but does the job. Should work for any number of dates, divisions. Will need a modification if there will be other categories besides Actual/Target.
UPDATED
The approach here is to get column names (by unpivoting one row), then calculate new column names with correct dates (here the “Shift” transformation comes in handy) and then rename columns in the main table using the “Rename with lookup” transformation. After that then main table can be unpivoted as usually.
merged_cells.morph (6.0 KB)
PS. It looks like we need a special transformation (“Fill right” ?) to deal with such cases.
Great solution! It does the jobs.
Many transformations I was not familiar with…
Good tutorial.
Thanks for the solution and your reactivity!!!
Regards. Michel.
Here is another solution. It’s a little shorter than the previous one, but it will work correctly only when all the Actual and Target values are numbers. Also this solution includes transformation from Date; Division; Actual /Target; Value; format to Date; Division; Actual value; Target Value; format.
Solution unpivot all the columns except [Division]. Then it fills down dates and Actual/Target “flags” and filters out redundant rows.
Transformation to Date; Division; Actual value; Target Value; format is made by splitting data into two separate tables with actual and target values and merging those tables by Division and Date column.
BestTransformationToDealWithThisFormating.morph (7.1 KB)
Regards, Andrew
Very beautiful solution!
UPDATE
Transformation Fill Right is available starting from version 3.6 and is intended for dealing with gaps in merged cells.
