Hi Team!
So I am working on bringing over data from our Certified Snowfall Totals subscription into a SQL Server Express instance. I was able to to bring over the locations data with no issues. However, I am struggling with Storm Data. I have been able to get everything into a cross tab and looking the way i need it but i am stuck on how to replicate the storm ID, Start date and End Date for multiple rows.
Main Attachment shows the work flow (please feel free to correct me if there is anything i can do more efficiently)
Attachment 1 shows the output data after JSON Parse. you will see that the data will have a storm id (0.id) start and end then multiple locations after.
In the workflow I scrub out the punctuation and numbers then use two multi row formulas to create recordIDs for the Storms and then the locations within the storm
Attachment 2 Shows the output after the record IDs are created. RecordID is for the Storm and RecordID2 is for the Location
Attachment 3 shows the output of the crosstab tool which is where i need assistance after. I need the blank fields for Storm_ID Start and End to replicate for each location that was affected by that storm.
Let me know if i need to explain further! Im sure i am missing some details. Thanks everyone for your help!!
