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

Pull down data, condition of same country and fill down dates.

regisb
7 - Meteor

Hi, here goes my new problem. 

 

I have data of interest rates for 3 countries, but this data is presented only by the date it was changed. My other column is every single day from 1990 to this week. I was able to combine both data and order by dates and country, but I want to "fill down" the interest rates until it reaches a new change, being careful to leave empty the cells when it changes country (not all countries started tracking their rates in 1980, and they all have different start dates).  

 

I'm attaching an excel: tab 1 is the interest rates, tab 2 the dates column and tab 3 is what I want it to look like. On Excel I was using this formula example: =IF(ISBLANK(C10579),D10578,C10579) ... But how do I avoid the program from pulling the data of the previous country to the next one? I have a feeling there is a conditional formula I'm missing... 

image.png

 

 

7 REPLIES 7
LordNeilLord
15 - Aurora

Hey @regisb

 

You need to use the Multirow formula tool here:

 

It will be something like

 

If !IsnUll([Interest rate]) then [Interest rate] Else [Row-1:Interest rate] Endif

 

Using groupby on Country

Claje
14 - Magnetar

Hi,


Take a look at the Multi-Row Formula tool.

 

Here's a quick example formula:

IF ISEMPTY([Interest Rate]) THEN [Row-1:PulledDownData] ELSE [Interest Rate] ENDIF

Here's a screenshot of it, note that I grouped by Country to make sure the data would not cross over to another country

 

claje_Multi_Row.PNG

jdunkerley79
ACE Emeritus
ACE Emeritus

You can do this using a multirow formula tool grouped by country with and expression like 

Iif(isnull([Interest Rate]),[Row-1:Interest Rate],[Interest Rate])
ponraj
13 - Pulsar

Here is the sample workflow for your case.  Hope this is helpful. 

 

WorkflowWorkflow

regisb
7 - Meteor

Seems to work!

 

One question, some of these interest rates are 0 (i.e. Euro), and Alteryx+Tableau will be confusing with those "0" that are before the actual tracking began. Is there a way to make the "0" before the start tracking date as empty/null? 

 

Ex: For Belgium, I want the values before 12/18/1998 (date they started tracking) to be empty/null... For Brazil the same applies for values before 3/5/1999, etc? 

LordNeilLord
15 - Aurora

@regisb

 

Set the "Values that don't exist" to NULL

zorro_1885
7 - Meteor

Hello

Very nice solution, thank you. I have a quick question how can you do same thing if you having 1,000 columns where you need to perform same action.

 

Thank you

 

Labels