I am not sure where to post my question which I posted on the welcome topic:
Dmitry,
Thank you for the “transpose” explanations on your blog. How do you keep the original order if columns headers were not enumerated in the first place. I see you rearranged them by ascending and descending order. How do I reorder the rows, mine are not enumerated. Second, if I did my transformation to one table, how do I apply if I have 100 tables of similar fashion which are also in-turn to be grouped into 4 (meaning I have 25 groups of similar grouping). How do I perform my repetitive action so that I may not do one by one to each. Thank you. Eyasu
The order of columns can be changed by simply dragging them -- this will create "Reorder columns" transformation automatically.
Reordering rows requires a column that can be used for sorting. If your original table didn't have one, you can merge row numbers from a helper table where the necessary order of rows is defined. See the example below:
Repetitive actions are best organized with iterations. You need to create 2 projects: one project that transforms 1 table as needed. Source file name should be defined using a project parameter in this project. The other project executes the 1st project 100 times, passing file name with table as the project parameter. It might sound complicated but it's actually a simple concept. Check out these tutorial articles:
Thank you so very much, this will save me countless of hours. I get the answer for the first question. For the second, I have to re-read to get it. I think you are making me to say Good bye to my traditional relational db and spread sheet applications. This is amazing.
To finalize my first step before I go to my repetitive action of my many tables, I am encountering syntax error with my if calculations. if x < y write 1, if x > y write -1 or else if x=y write 0. What would be the right in put in my expression to calculate in new columns? (x and y are some of the columns that contain values.)
Thanks.
Wow, perfect. Way more to go deeper with this seemingly small software but colossal in power. Now I will continue with my repetitive action and computation. Thank you.
Before I go to my mass repetitive action, I have to split my table rows into three to do three group of summation of my if calculations. Is it possible to split rows into groups? I understand that this is not a spread sheet where we simply insert rows to do partial computation in a cell.
Dmitry,
May I please ask one more question. In the transformation section, there is a possibility of “trim table” from bottom or from top to show certain rows (say out of 30 rows show only 5 rows from bottom or top) which is quite interesting. Is there a possibility to show certain columns to trim from far left or far right (say out of 30 columns show only 5 columns from far left or from far right). If so, I can reduce my transformation by 40% because I can avoid transposing.
New issue
Since my transpose columns are coming from my trimmed row dates (showing me the most recent certain dates), and I use them for calculations, I encounter syntax error as my table updates every day from external source. To avoid that, is there possibility to insert reorder columns as we do insert reorder rows in the transformation so that I can use those fixed numbers instead of the changing dates every day in my columns calculations?
To give you a better idea I have sent you image of part of my data analysis. day 1 is the most recent date.
Typically, grouping rows is done by adding one more column (dimension) that can be used for grouping. Although, I'm not sure I understand your question correctly. Can you please provide an example in Excel?
I assume column names are not fixed, because otherwise you could use "Select columns" or "Remove columns" transformation. If column names are not fixed, but the number of columns is fixed then you use the following workaround:
Save the dataset into a temporary file.
Load the dataset from the temporary files with ticked checkbox "Columns don't have headers". In this mode the headers become 1st row, and columns are labeled as Column1, Column2, Column3, etc.
Use "Select columns" or "Remove columns" to remove particular columns
Use "Label columns" transformation to use the headers from 1st row as the column labels.
It would be convenient to make steps 2-4) as a separate project that can be called from the main project using "Call project" transformation.
If number of columns is not fixed, then I don't think there is a way to remove N rightmost or leftmost columns. Probably that would be a candidate for Feature requests.
It looks like you're trying to calculate some IF statements for last three column of a matrix table, but its column names are dates and therefore they are changing which breaks expressions and causes "Column not found" error, if I understood you correctly. In this case I would suggest extracting last 3 columns in a derived table, then use the workaround with saving/loading without headers that I described above to get column names as Column1, Column2, Column3. Now when column names are fixed you can use then in the IF expressions. The resulting new columns can be appended back to the main dataset using "Append" transformation in "Append columns" mode.
I can solve the row grouping by working around through the method you gave me by creating helper reorder table. I can split the rows into group that way.
Your last illustration may solve my problem if I fully understand “Append columns” as the syntax error is coming as new data arrives with new dates. Yes I email you an image of what I am working, as I could not pm you in this forum.
“It looks like you’re trying to calculate some IF statements for last three column of a matrix table, but its column names are dates and therefore they are changing which breaks expressions and causes “Column not found” error,”
That is exactly what I wanted to do. Even with headers columns 1,2,3…as new data arrives the column headers increase and the syntax errors occurred. I couldn’t really figure out “Append Columns” and “Derives” as applicable to my case. I wanted to do my if calculations for the last 4 recent columns in order to get for day 3, day 2, day 1 results consistently as new data arrives daily. Whole night and day struggling in vain. Your help would be appreciated.
Here you go! See the attached project. In the project you can change parameter Date to any date in December 2016 and it will generate random data from December 1st to that date, then transpose it and calculate an IF statement for last two columns. The expression works correctly, regardless of the selected date range.
Following your concept I did it all in one table with 4 transformations. Then, since I have more than 30 items in my rows to be classified in group with each group different if calculations I split them with helper table you showed me before, and do my calculations.
There is no way I could have arrived here my self no matter how much I read and reread every material. Thank you so much. I am impressed. Now I will continue with my repetitive action with the rest of my tables.
My first impression about EASY-MORPH with my limited small glimpse of its potential:
You will laugh if you know I throw it before and un-installed it from my computer few months back, saying it is another crap with out even knowing I will even ever get a help if I ask. You will also laugh what I had been doing to do what I asked you above. I installed mysql and mysql work bench. I used special helper software to import multiple csv files. I did connector to retrieve my tables into libre base to attempt to create my relational database. Since most db applications more or less are limited to do calculations, I was forced to export and import data into libre calc. Imagine, it is not even linked and I have to do it manually. I was exhausted and came back to the crap I throw out just while I was googling for other craps. Now I find myself doing everything in one software, “EASY-MORPH” linked together all in all. Forgive me.
Now I came into a conclusion this Goldmine called “easy-morph” could have also does real-time analysis. Does easy-morph has capability of importing charts with real time data or else even embedded into? Could easy-morph pull data from on-line real-time values and update data in real-time? Just wondering and curious what this giant could have perform.
This is one of the best user testimonials about EasyMorph I've ever heard! Thank you, @eyasu Apparently, we should do a better job explaining what EasyMorph can actually do. If you think we should be adding something to our main web-site (or removing) in order to help other people get a better idea of EasyMorph -- you're welcome to share your suggestions in this topic that I've just created: "Anything we should add to / remove from easymorph.com?"
EasyMorph is intended for batched and ad hoc data transformations of medium and high complexity, that would otherwise require scripting or programming. While technically it is possible to run EM projects every 5 minutes or so for quasi-real-time updates, by design it's not a real-time application.
Apparently, we should do a better job explaining what EasyMorph can actually do. If you think we should be adding something to our main web-site (or removing) in order to help other people get a better idea of EasyMorph -- you're welcome to share your suggestions in this topic that I've just created: