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!!
Solved! Go to Solution.
I think that you can use a multi-row tool configured as shown below. The formula will be
IF IsNull([ID])
THEN [Row-1:ID]
ELSE [ID]
ENDIF
where the [ID] field will be the column that you want to update and you make sure that it is set to update existing field where that field is selected.
Hi Brandon,
Unfortunately that did not work. Or maybe I did properly set the formula.
Please see below:
So basically what I need is for any STORM_ID, START or END Field that is null to populate with the values listed at the start of each RecordID set.
RecordID 1 should list those values for all records with that ID so on and so fourth.
Ah, this is because the cells are empty, not null. Can you change the IsNull functions to IsEmpty instead? I think that should fix things for you
EXCELLENT! Thank you very much!