Tables with Variable number of rows
BeantwoordHi There - I have simple CSV file with a few columns of data on a securities portfolio. Each row is a separate investment security. Each month end the total number of securities changes (and therefore the number of rows changes). I created a table in wData and also created a query to filter out unneeded rows. My question is that I want then to populate a table in Wdesk for all investment securities as of a particular month end. Unlike using the INDEX function to source a fixed GL account and date parameter, I mere just want to source all rows from the query that have say a date of '04-30-2020' - realizing that each month there may be more or less rows from the month before.
Ultimately i want to be able to drop the CSV file into a box folder and make a chain to fetch it, run the query and then serve it up to the Wdesk and into my monthly close deck.
Can you point me to where i can find information on how to do this? Many thanks for any help.
-
Hi Bradley, you could create a connected sheet to get the data from the query. With the data loaded into your Wdata Table, create a query with a parameter to filter on date (as well as any additional filters needed). You can find documentation on how to create query parameters at https://support.workiva.com/hc/en-us/articles/360040227931-Manage-Query-Parameters#section1. Next connect a spreadsheet to your query by following the steps here https://support.workiva.com/hc/en-us/articles/360035643272-Connect-Wdata-Queries-to-Spreadsheets. Also included in the second link is how to update the spreadsheet when new data needs to be pulled in.
If you're doing this with Chains rather than connected sheets, it would be a similar process. First you can retrieve the file from Box using the Box Commands and load it into your Wdata Table. Box Commands are documented here https://support.workiva.com/hc/en-us/articles/360036005671-Box-Commands. Next use Export Query Result https://support.workiva.com/hc/en-us/articles/360036005471-Workiva-Commands#section21 which will fetch the data from the query and then writes the data to a spreadsheet. Each time the Chain runs, the data retrieved from the query would be pushed to the spreadsheet.
0
Thanks so much Jeff. Really appreciate the information. We got this to work.
0U moet u aanmelden om een opmerking te plaatsen.
Opmerkingen
2 opmerkingen