Is persisting query results possible?

[gabrie...@gmail.com]

Hello!

I'm new to SlamData and have been playing with the 4.1.1 community edition for the past week in hopes to integrating it to an existing system I'm working on. Previously most of my work was focused on design aspects and front-end logic so I'm relatively new to this world. I have two questions that follow the same theme, something that has been delaying my advance.

1) Is there a way to "set" a variable outside the Variable Card, or a way to reference the result from previous query cards?

It seems weird to me to not be possible to create new variables or to access query-created tables without using Markdowns or outside the Variables card(only at the beginning of the deck)

2) The query results of a deck get progressively stored in the 'results' variable. is there a way to reference a specific past result inside that variable?

Thank you.

[Jeff Carr]

You can query from any previous results set by reference or by just using a new query card. Here is an example:

SELECT

  o.order_key AS OrderKey,

  o.purchase_date AS PurchaseDate,

  c.last_name || ", " || c.first_name AS Name,

  c.city AS City,

  c.state AS State,

  i.clothing_size AS Size,

  i.clothing_type AS Type,

  i.clothing_color AS Color,

  i.price AS UnitPrice,

  oi.qty AS Quantity,

  i.price * oi.qty AS PricePaid,

  SUM(i.price * oi.qty) AS TotalPaid,

  i.clothing_brand AS Brand

FROM

  :results AS c

JOIN `/mongodb/data/orders` AS o

ON c.customer_key = o.customer_key

JOIN `/mongodb/data/ordered_items` AS oi

ON oi.order_key = o.order_key

JOIN `/mongodb/data/inventory_items` AS i

ON i.item_key = oi.item_key

GROUP BY

  i.price * oi.qty

If you can share more about the actual workflow you are trying to build, the data, and then expected output that helps.

Jeff

[gabrie...@gmail.com]

I'll try to exemplify one of my workflow problems with a simple sample situation.

Let's say I have a collection of documents called "Measures" and each Measure has, among other things:

- A measure.sensorId field representing the origins of said measure(There is also a collection for sensors);

- A measure.label field containing a String that represents the nature of the measurement (ex.: "Temperature", "Voltage", etc);

- A measure.val field containing the numeric value of the measurement.

- A measure.ts field containing a timestamp epoch value from when the measurement was taken

Now, if I want to, say, plot two overlapping graphs comparing the same kind of measurements from two different sensors (for simplicity, I'll assume here that they have roughly the same amount of measures taken at roughly the same time). 

What I'm doing so far is, to query the measures collection for all measures originating from either one of the sensors:  

SELECT
    measure.label as Label,
    measure.val as Value,
    TO_TIMESTAMP(measure.ts) as Time
FROM
    `/foo/bar/measures` as measure
WHERE
    measure.label = "Temperature" AND

    (measure.sensor_id = :Sensor1 OR
    measure.sensor_id = :Sensor2)
GROUP BY
    measure.sensor_id

This gives me all pertinent temperature measures from both sensors as a result. How would I go about building two different sets of measures, one for each sensor_id, so I could plot them separately in the same graph?

    

    

[do...@slamdata.com]

If I understand how you want the chart to look, you want to modify your SQL statement to have the measure.sensor_id field returned from your statement.

SELECT
    measure.label as Label,
    measure.val as Value

,
    measure.sensor_id as Sensor,


    TO_TIMESTAMP(measure.ts) as Time
FROM
    `/foo/bar/measures` as measure
WHERE
    measure.label = "Temperature" AND

    (measure.sensor_id = :Sensor1 OR
    measure.sensor_id = :Sensor2)
GROUP BY
    measure.sensor_id



With that you can add a new Line Chart card with the following

Dimension: Time

Measure: Value

Series: Sensor

This will plot the data for both :Sensor1 and :Sensor2 on the same chart. Removing this portion of the SQL WHERE clause would allow you to see all sensors on the same chart. You can toggle on/off individual series by clicking on their item in the legend at the top of the chart.

One thing to note is that by default the measure will be the sum of all values for a given dimension point. You might want to change that to average if you have multiple sensor readings for the same time period.

Hopefully this has answered your question. If not let me know.