Automatically use the last 300 days from the column filter

[Gavin02]

Column Filter automated.knwf (270.9 KB)
Hello KNIME Community
I have built a workflow that shows me the share history of the last few years. The problem is that I have manually set the ColumnFilter to the last 300 days in order to evaluate the data. Now I would like to have the last 300 days evaluated automatically. What solution can I use? My Workflow is attached.
Many thanks in advance

[rfeigel]

Do you want 300 days prior to the current execution date?

[Gavin02]

Yes Right, If I execute the Workflow today I want to have the Data of today and the last 299 Days of this API.

[MartinDDDD]

take a look at this… I changed a little bit how to get JSON into a table and then used current date to 300 days before as a range…

Test 2.knwf (345.3 KB)

[Gavin02]

Hey @MartinDDDD
Thank you very much for your input!
I was able to adapt my previous workflow and implement the solution, but not as clean and nice as yours. I have also added the ticker name in the Interactive Dashboard so it changes automatically. However, I have 2 questions.

1. Can you roughly explain to me what the JSON Path node and Ungroup node did in this case, I could not figure it out well and Chat-GPT also explained it weird.

2. Is there a way to connect to the table creator node into the interactive dashboard. So that you can change the ticker of the stock without changing the ticker manually in the table creator? If the solution is too complex, then I just change it manually
   Many thanks in advance
   Test 5.knwf (317.3 KB)

[MartinDDDD]

Hey there,

great job - the app works very well! Happy to explain a little more. Let’s start with 1):

• your approach with using JSON to table works, but makes the whole thing with identifying the last 300 days etc. difficult. when I inspected the API response I noticed that the “series” key hold a list ([…]) of Objects ([{…},{…}]), where each object represents the ticker information of one day:
  
• With JSON Path you can extract specific elements out of a JSON structure - when clicking into the List of the Series Objects the Option “Add collection Query” activates - when clicking that you get a JSON Path Query that will extract each individual object as a row into your table. When you activate the “List” check box it extracts the key-value pairs into a list
  
  
• A collection or List can be “expanded” i.e. rather than having one column with a list object you will get one column per item in the list object - and this you do with the “Ungroup” node. As the List object contains only one of the JSON Objects containing the information for different days, we now get a column that has in every row just one JSON Object - one row with information for a specific day
  
  
• when you now use JSON to Table on that new column you directly get the data for each key-value pair in the JSON object into different columns… and with the data for each day now in a row each all the other steps get a little easier
  
  

So now regarding 2:

• you can use a string widget for the user to enter a ticker - I called the variable Name “Ticker” so that the column that gets created when adding a Variable to Table Row Node has the same name as your column in Table creator. I have also added a refresh button that users can click after entering a new ticker:
  
• I then highlighted all the nodes (removed your other one’s…) and created a new component
  
  

Here is the workflow:

Test 5.knwf (105.6 KB)

[ActionAndi]

I‘m wondering if its possible to adjust the post string to get just the data of the desired time interval. Now it just downloads all available data and filters afterwards.

[Gavin02]

Thank you for the solution and your explanation, now I understood the Workflow.
Happy holidays

[rfeigel]

I took a quick look at the API and there doesn’t appear to be a way to pass it time bounds. I may have missed it.