I need to Left join 2 tables, one is part of our normal DB (postgresql) and the other is an uploaded CSV file. the csv file data structure always appears with a numerical array above the column keys.
eg
CSV_Table
-0 2keys
-name
-user_id
-1 2keys
-name
-user_id
-2 2keys
-name
-user_id
-and so on
the table to left join to is structured normally
query1_SQL_Table
-name
-users_id
How would I go about joining these 2 datasets? is there a ways to format the uploaded CSV to be in the correct format. It displays normally on a table... I have tried uploading it as an xlsx, using google sheets and other things but always get the same result
I can display both tables at once if I select * form {{query1_SQL_Table.data}}, {{upload1.parsedvalue}},
but when you {{upload1.parsedvalue.name}}=undefined
{{upload1.parsedvalue['0'].name}} = gives you the name under the name key in that 0 entry
but i want all names from all entries 0-500
I'm having the same issue. It would be greatly appreciated if someone could offer some guidance.
I have a .csv upload with two columns (primary_key, value). This is uploaded by the user in order to update the database with the new value given in the .csv file
Then there's an SQL query that is pulling the same primary_key along with a few other columns. The issue is that the "value" column is pulling whatever value is selected in the .csv. So every row in the table has the same value instead of pulling for the primary_key value
Please let me know if any other information would be helpful
make sure the switch in the uploader switch is set to parse files.
I created a sql query called upload to "hold" the uploaded data, and executes with input changes
select * from {{(fileButton1.parsedValue['0'])}}
(this may not be necessary but it worked.)
reference the above query in your query that joins instead of the fileButton.
(
select
xx.value,
ud.value,
xx.primary_key,
ud.primary_key
from database.data as xx
left join {{upload.data}} as ud on ud.primary_key = xx.primary_key )
There is likely a better way to do this but that is what worked for me
Thanks so much for circling back to share this solution, @Mintbricks! Sounds like you're doing this with the Query JSON with SQL resource type. (Query JSON with SQL | Retool Docs may be a useful doc for anyone else looking at this thread ) It might even be possible to do what you described with one query, but the above should work great! Appreciate you sharing so others who find this thread can follow along