Chart with data from other table

DamianZgl · 06-06-2024 05:33 AM

Hi everyone,
I need help with merging two tables, more specifically:
I have one table with raw data regarding some speeds. Its structure looks like this: I have a column timestamp, which contains the time of reading the speeds, the next columns are columns simply called a certain variable, and they contain just those speeds.
The second table, in turn, is the table on the basis of which I built myself a Deck view, which groups these speeds for me accordingly.

My problem is that I want it to also show me a line graph when I enter the deck details.
I have no idea how to do this, I've tried many solutions and unfortunately no success. I'll just hint that the label of this graph is supposed to be the timestamp column, and the values in the graph are supposed to be a certain column, depending on which deck I press.

If something is not clear in what I wrote then please write and I will try to explain it

Suvrutt_Gurjar

@DamianZgl wrote:

The second table, in turn, is the table on the basis of which I built myself a Deck view, which groups these speeds for me accordingly.

I think you may want to elaborate above.

You may also want to mention if the two tables have any reference relationship and what kind of graph , meaning based on what grouping you wish to have these graphs. It is understood that the timestamp ( x axis) and speed ( Y axis) but grouping by say certain vehicle/ user etc, you may want to elaborate.

Typically in such cases screenshot of table column names and their types help. Such as below where Orders table and Order details table have reference relationship. Screenshot of concerned views also help.

DamianZgl

@Suvrutt_Gurjar wrote:
I think you may want to elaborate above.

Sure, I will try to explain.
My second table is the table based on which I built the deck view, in order to have grouped speeds and other values from different tables. It's hard to explain, but maybe a screenshot will tell more:

In this table, I pull from the data table only the last value, that is the most recent entry, to see the current speeds.

In the column, for example 1_IloscProduktow, I have a formula that, based on the value in the Ref4 column, substitutes for me the corresponding value from a column in another table. The rest of the columns work very similarly.

Mainly, this table was created for this purpose, since I can't edit the data table. It has to keep a certain table structure and I can't add any columns that I need to do for this app.

The data table look very simple, because i did not changed anything there:

@Suvrutt_Gurjar wrote:
You may also want to mention if the two tables have any reference relationship and what kind of graph , meaning based on what grouping you wish to have these graphs. It is understood that the timestamp ( x axis) and speed ( Y axis) but grouping by say certain vehicle/ user etc, you may want to elaborate.

Tables, unfortunately, do not have any reference relationship. I don't really know how I could implement this in my case, because I can't edit the data table, but I can only add virtual columns to it.

The chart I need is: line chart - one column with speed (Y axis) and the other column timestamp (X axis).

I need as many charts as I have columns with data, as I need a chart for each speed

The only thing you would need to pay attention to is that if, for example, [Ref4] = Machine1, then when you click on a particular deck, the graph from that machine will be displayed in the details.

Suvrutt_Gurjar

Thank you for the details. I am sorry, I still do not understand your table structure and requirement of charts because there still are some unknowns such as below as the exact structure of column [Ref4] relation to Machines etc. is not clear.

@DamianZgl wrote:

The only thing you would need to pay attention to is that if, for example, [Ref4] = Machine1, then when you click on a particular deck, the graph from that machine will be displayed in the details.

Here are a few observations, if may add

1. Your overall schema seems to be a bit unconventional in the absence of reference relationship in tables and still pulling extensive data between tables.

2, Having virtual columns to pull data from other tables could start getting sync time expensive in the absence of reference relationship.

However as you mentioned you are the best judge of not utilizing references if it is not possible for you.

Anyway, with existing set up you, may want to consider using column series line charts. AppSheet considers label of the table as X axis value. So you wish to make timestamp as label column for the "Second Table"

Somebody may have better suggestions for you based on the details you have shared.

Note: Also if you are considering extensive charting and analysis based on that charting, you may want to consider using Looker Studio because AppSheet's native charting capabilities are somewhat basic.

Solved: [Preview] Rich reporting with Data Studio - Google Cloud Community