Jim Dehner - sorry to bother, but do you know of other ways to seek help on this? I am getting nowhere with NOAA. I think I am pretty clear describing what I need above, I wonder if really there is none out there who could crack this.
This is one of those cases that can't fully be solved with Tableau Prep (at least, not yet!) When something like this idea is implemented, Tableau Prep will be able to fully handle these types of problems (feel free to vote it up!) https://community.tableau.com/ideas/8732. The basic need is to be able to work across rows like you can with table calculations in Tableau Desktop.
In the meantime, you can do just a bit more manipulation in Excel to get it to a state that makes it doable in Tableau Prep.
Here was my approach:
I added two columns, the Key and Row Type to carry information down the table. The Row Type is calculated in Excel as:
Basically, it's looking at the D Column which is either a name or time (string or number) and using that to determine if the row is a header or detail. That's going to be useful in Tableau Prep, but the Key column is the ... key (sorry).
This uses the same basic logic but based on whether it's detail or header it either gets the storm ID or copies it from the previous result (I cheated by setting A2 to the value, but from that point on it works). The calculation is:
=IF(ISNUMBER(D3), A2, C3)
Now you have a dataset that Tableau Prep can handle because you have an identification for each record. The basic idea is to split out the header records and detail records, clean them up a bit, then join them back together so you get all the header information for each detail record. (I've attached the flow so you can dig into it a bit more)
Assigning a row number is another challenge in Prep. The idea I mentioned would allow you to do this, as would this one: https://community.tableau.com/ideas/8714
Since you have the Date & Time (which I calculated from the date and time fields), you do have an order. Likely, that field alone would be enough in Tableau Desktop to allow you to do anything you wanted (e.g. the Pages shelf) or you could use Rank() or Index() in Tableau Desktop to get the specific order.
If you truly do need a row order in the data set, then check out this post for some ideas on how you can get it: https://vizpainter.com/sorting-rank-ranking-in-tableau-prep/
Hope that helps!
Hurricanes.tflx 546.6 KB
Joshua - thanks for leading me in the right direction. I don't mind doing a bit of modification in Excel, if that's the only route for now.
I really like this approach and I think we are close, but, I was unable to replicate the Key column. See attached. It seems like the formula stops working for me as soon as it hits the next set of storm data.
As far as numbering rows, the only reason I need something like that is to have a unique ID for each recording of a storm position. But I think the Key column can solve that, if I can get it right in Excel. You would think that Date and time alone would be sufficient as a Unique ID, but as I found out, sometimes there are more than one storm that are active (duuh) and they check and record their position at the same time and date. So - either a Name or a storm number (ideally both) needs to be brought in as an additional component of a Unique ID.
Can you help me get that Key column right and I will try to take it from there, please? What am I doing wrong there?
Josh - brilliant! I made a few tweaks to the flow as I needed some other info in addition to what you proposed, but I really appreciate your help.