Need help with combining data from Sheets and MySQL

Queries and Resources
,
1

I have two resources, one is a mysql query and another one is a google sheets query.

The mysql query is as follows:
It's resource name is InventorySummaryMySQL

select 
  SKUID,
  SUM(CASE WHEN (Status = 'FABBED' AND Location = 'FABYARD') THEN Qty END) as QinFab,
  SUM(CASE WHEN (Status = 'FABBED' AND Location = 'GALVYARD') THEN Qty END) as QinGalvYard,
  SUM(CASE WHEN (Status = 'FABBED' AND Location = 'GALVANIZER') THEN Qty END) as QinGalvanizer,
  SUM(CASE WHEN (Status = 'READY' AND Location = 'SHIPPING') THEN Qty END) as QReadyToShip
from Inventory
GROUP BY SKUID

a sheets query as follows that joins to InventorySummaryMySQL

SELECT
  SKUID,
  QinFab,
  QinGalvYard,
  QinGalvanizer,
  QReadyToShip,
  [qstat/On Order] AS OnOrder,
  [qstat/SSIC] AS SSIC,
  [qstat/Shipped] AS Shipped
FROM {{ formatDataAsArray(InventorySummaryMySQL.data)}} as SQLView LEFT JOIN {{ SheetSKU.data }} AS SheetSKU
ON SheetSKU.[🔒 Row ID] = SQLView.SKUID

SheetSKU is just a google sheets resource of a sheet in a google sheet.

This query doesn't work. I can't figure out why. For some reason, when I hover over "data" on

{{ formatDataAsArray(InventorySummaryMySQL.data) }}

The data shows transposed! I can't figure out why this data won't join.

image
image1704×726 101 KB

2

Hey @appiman :wave: Just to clarify -- you are querying a MySQL resource and a Google Sheets resource (using our native integration). You then would like to join this data using a Query JSON with SQL query?

3

Yes, I am trying to join the two, I think it would work if the results from the SQL query weren’t transposed.

I can’t figure out why it’s being transposed.

4

@appiman would you mind clarifying what you mean by transposed? I see that you are hovering over InventorySummaryMySQL.data in the screenshot, and you are formatting this data using formatDataAsArray. If you hover over the last two brackets in {{ formatDataAsArray(InventorySummaryMySQL.data) }} do you see the data formatted as you expect? If you format the data as you like outside of this query is it running successfully?

5

Transposed means that rows turn on columns and the columns turn to rows. Basically rotates the data 90 degrees. Like matrix algebra transposition.

Anyway, I just worked around it. I scrapped the mysql database because I couldn't figure this out and my client was hammering me for results, so the version that I showed no longer exists...

I guess we can close this topic. If I can figure out how !

6

Hey appiman I am trying to do the same thing and wondering if you found a solution that works

7

Hey @technopath Can you share the shape of the data from each resource? This is likely due to a mismatch in the shapes (ie: an array of objects vs an object of arrays) but would have to see the data to figure this out for you.

8

@joeBumbaca thank you so much for getting back to me, please check my post for more details.

On a different point, I am having trouble joining data returned from a MySQL database with data returned from a retool table. What is the best way to do that?