When starting with a some sort of text file as input (tab delimited) I am missing a step to override the guess DT does for each column/value. In Excel import there is a step where you can select each column and set type to text / number / date.
Hopefully I just was blind.
Thank you.
Thomas
You donât need to set a type, EDT internally it is all text. EDT will analyse the first rows and will take the values as type definition for the operations. But I think Admin can give more detailed information.
@tschloss Olaf is correct. We donât force you to set a type for each column. Internally values are stored as text. But different transforms might interpret a column as numeric or date, where appropriate. For example, the Sort transform will (by default) look at the values in a column and decide whether to sort it as text, date or numeric.
This approach is very flexible.
Thanks to both of you for your quick responses! - I stumbled across this at creating a pivot table. Both column and row headers are supposed to be numbers. But the pivot came with cols and rows sorted as text. I also have an ID column, which needs to treated as text although only digits occur.
Until now this only showed up when exporting, but maybe there are places where the datatype might make a difference during mangling also.
But I understood that there is no feature to assign types during input and as a rare user (just upgraded to v2) I can work around it.
Thanks again.
You can set the data types for the columns, if you are writing to Excel file as output.
Here is content from the help file.
Set Formatting depending on how you want to set the Excel cell formatting (only available for Excel files).
Automatic to let Easy Data Transform decide cell formatting depending on the contents of each column.
General to set the cell format for all columns to âGeneralâ.
Manual to choose the cell format of each column as:
Automatic (based on column contents).
Boolean (expects true or false, not case sensitive)
Date (expects a date format in Preferences).
General.
Number (expects a real or integer number, e.g. 123 or 123.456)
Text.
Formula (expects starting with =).
Time (expects hh:mm:ss or hh:mm, e.g. 13:59:01 or 13:59).
Hyperlink (expects a hyperlink, e.g. https://www.easydatatransform.com).
Choose the data types for your columns and they will appear accordingly in the Excel.
You should be able to use a Sort transform after the Pivot to get the exact row order you want.
You can also re-order the columns by name with 1 click in Reorder Cols.
Currently this will only re-order as text.
Ah thanks good idea. Try it asap. Hope it can sort columns and rows (without the âtotalsâ in both dimensions).
Cheers, Thomas
It will treat the sum rows and columns the same as any other row or column. But you could omit them by unchecking add totals in Pivot, do your Sort then add the sum rows and columns using Stats.
Perhaps we could make the row and column sorting more intelligent in Pivot. I will make a note of it.
Pivot and sequence of columns and rows can be a beast. In case I have to do reporting with a given table structure I work with a âworkaroundâ.
E.g. I want te report on some Teams which must be present in a non-alphabetic order. And I want to report on Quality classes 1 to 4. In case a quality class doesnât exit on in input data in some cases a column, like Q3 in the screenshot wouldnât be shown in the table. In my regular reporting I have it quite often that sometime some values donât exist, but the reporting table should be in stable, showing all possible values/columns.
In such cases I work with help tables just with the headers in the required order an Stack transformation (and even transpose).
An example you have in the attached file. Creating the row order Team A, C, B and creating column for Q2 which is not in the source data. For sure it worse the effort in case of repeatitive use.
Pivot and field order.transform (4.9 KB)
@Olaf
Not sure there is an easy solution to that. I will have a think about it.
Donât think so. I just stated it to give a recommendation how to handle. Used such combination of stack and transpose already a lot of times, at least no issue for me. But maybe this helps others with similar challenge.
