Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Help Needed Certified Snowfall Totals API

jonathan_lyon
6 - Meteoroid

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!!

 

 

4 REPLIES 4
BrandonB
Alteryx
Alteryx

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. 

 

multi row.png

jonathan_lyon
6 - Meteoroid

Hi Brandon,

 

Unfortunately that did not work. Or maybe I did properly set the formula.

 

Please see below:

 STORM_ID.PNG

 

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. 

BrandonB
Alteryx
Alteryx

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

jonathan_lyon
6 - Meteoroid

EXCELLENT! Thank you very much! 

Labels