How to treat rows as columns?

Hi Ivo,

thanks for your interest in ARIS MashZone. I think the first task can be accomplished without treating rows as columns.

The reason is the usage of the bar chart. In fact it does not differ from the column chart so much. Both charts have one axis for categories and another for numeric values. This has no impact on your data feed at all since during data assignment within the MashApp Composer you can transpose the axis.

Bar chart: Y Axis comes first

Bar chart: Y Axis comes last

To aggregate ten projects where the data originates from different sources you can make use of the Operator "Data feeds Concatenate". The operator has two inputs and appends the rows from one, e.g. Excel file, to the other. I expect that in your example from each Excel file is one row with two columns returned.

The second question: I am not quite sure if I understand you correctly. But I guess it can be achieved either using the filter operator or just use the aggregate functions during data assignment.

Stephan,

Yes, you are right. The first question has nothing to do with rows. I want to get two values from each file and the only way I found was to use get data from range. Then I used Concatenate (In fact I tryed almost all operators) but the best result I gan get, after deleting unnecessary columns, was to the two new columns without matching:

Projects (new column) :: Person-days (new column)

projname 1

projname 2

projname 3

....

                                     :: no-of-pdays 1

                                     :: no-of-pday 2

                                     ...

So I got not matching. And in the example you give it is manual input and I have no problems with that. But how to get each couple (projname-pdays) when they belong to different cells, it can't columns, it can't be row and it can't be range. I didn't see forth option.

For the second thing all I want is to treat a raw as a column with the first value being the title and get all other values (in my case months) to correspond to calues of another row, the couples belonging to the same column.

I haven't tried the filter operator or aggregate but have tested what they do in another case and I have no idea how to use them to solve my case.

Hi,

first of all: The MashApps I created the screenshots with are available here. The ZIP-Archive contains three MashApps which you can import into your MashZone installation by extracting it into the importexport directory which is located in your MashZone server installation. Afterward you can select these files from Home - Administration - Import.

Regarding reading tuples from different positions in Excel: If using data ranges is no option you may read each Excel file twice. Source one reads one cell containing project name the latter the days?

If it remains unclear, please send me such an Excel file (containing fake data of course) and I will get a better understanding.

Regarding columns: If you look at the data feed named "Demo Green Car Potential Buyers" you will see that MashZone will _NOT_ calculate (or aggregate) over columns since ‒ from a MashZone point of view ‒ columns are usually of different data types or semantics. If the number of columns is constant you my follow the "Potential Buyers" example. Otherwise I suggest you consider to rearrange your source file.

Stephan,

I have a problem which is similar to Ivo's second question. I hope none of you would mind me using this topic to ask my question.

I have an excel report which I am not allowed to change and I need to create a chart in MashZone with months on the x axis and total and weighted revenue on the y axis. However the report has a separate column for every month of the year and in MashZone dates should be all entered in a single column as far as i understand. Take a look at the report structure:

If I used the crosstab functionality I would get all the months on my x axis. But this would also put columns like Industry and Country on the axis and I don't want that. I want to use the Industry as a dropdown menu on my mashapp, but that is a separate issue.

I would really appreciate it if you can give me a hint how to crack this.

Veselin

Hi Veselin,

since there are three columns per month the cross tab does not work out of the box. First skip column A and B and focus on the data range starting at C2 (you can do so by specifying an Excel rage in the "Import data range from rage" option in the MashZone data source.

Reading the range using the cross tab feature results in a three column feed where there are groups of three rows belonging together. We need to separate them.

The goal is to move every second and third group member into a separate column next to Jan, Feb, etc.

To do so we first insert a new numeric column with numbers in ascending order. After this the feed is duplicated (in the screen below two times, you need it three times - once for probability and another time for weighted).

In the left path filter to select rows for a given month only. Set the filter for the other path to select every second group member (or third). Now adjust the ascending number by subtracting 1 for the second path, 2 respectively. The number will be the criterion to combine rows from different paths again:

Finally you can drop unnecessary columns and rename the remaining in a proper way. Please find the complete result below. The first two columns omitted initially could be added by reading the same Excel file again (using a second data source). This time with list table option instead and the first three columns, only. Use the country column to combine both feeds again.

Regards

Stephan