maud villedey Posted July 5, 2020 Share Posted July 5, 2020 Hello, I am desperetly trying to solve an issue I have on descrete data. My table describes the behaviour of pumps in wells over time. In each well, pumps can be on position Lower, Middle and Upper, and each pump works idependently in each well. (U and L can be openned at the same time). For each pump, corresponding openning status is given for given times. The dates correspond to the event dates (openning or closing). 100 means open, 0 means closed. I would like to make this descrete table continuous in order to display Opening status over time from 1997 till today, depending on the position of the pump in the well. Here is an exemple of my table : Well Cycle_Date FU Position Opening Status Well1 01/01/2017 L5 Upper 0 Well2 15/07/1999 L7 Lower 100 Well1 24/06/2018 L5 Upper 100 Well3 29/06/2002 L6 Middle 0 Well2 09/12/1999 L7 Middle 0 Well1 01/09/2018 L3 Lower 100 Well3 27/06/2001 L6 Middle 100 My purpose is to have the detailed openning status for each date from 1997 till today for each pump in each well. How may I do that ( I already have a master table with my full list of dates). Thank you very much Kind regards Maud Link to comment Share on other sites More sharing options...
Fabian Duerr Posted July 12, 2020 Share Posted July 12, 2020 (1) The first thing you need to do is to create a table that has all rows that you need. So one row for each date, well, FU and position. (2) Then merge your Opening Stauts column form the original data table to the complet table (merge by: date, well, FU and position). (3) Then create a new calculated column that is grouping your well-match-position, for example by using a simple concatenate function. Let's call it 'mygroup'. (4) And finally one more calculated column with the following code: Last([Opening Status]) OVER (Intersect([mygroup],AllPrevious([Cycle_Date]))) I created a more simple dummy data set for clarification. Just 2 wells, 2 FUs, 2 Position and only one week: Cycle_Date Well FU Position Opening Status 1/1/2017 Well1 L1 Upper 0 1/4/2017 Well1 L1 Upper 100 1/5/2017 Well1 L2 Upper 100 1/7/2017 Well1 L2 Upper 0 1/1/2017 Well2 L1 Upper 0 1/5/2017 Well2 L1 Upper 100 1/4/2017 Well2 L2 Upper 0 1/3/2017 Well2 L2 Upper 0 1/7/2017 Well1 L1 Lower 100 1/1/2017 Well1 L1 Lower 0 1/5/2017 Well1 L2 Lower 100 1/7/2017 Well1 L2 Lower 0 1/3/2017 Well2 L1 Lower 0 1/1/2017 Well2 L1 Lower 100 1/7/2017 Well2 L2 Lower 0 1/5/2017 Well2 L2 Lower 100 To achieve (1) you can use multiple cross joins of tabels. You need a data table, a well table, a FU table, and a position table. You can perform this taks outside of spotfire. Spotfire has no cross join option, but there is a workaround (using insert cloumns with full outer joins): https://support.tibco.com/s/article/Implementing-Cross-Join-on-two-diffe... For the data merge (2) you sipmly use 'insert columns'. And (3) and (4) should be clear. So here's the final result: Upper row old status, lower row new status DXP file is attached. (This was a tough one...) Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now