Check if field exists, if not append to column with conditions

[hal_dal]

Hi all,

I'm having a hard time to figure out which tools/logic I should use to acieve this.

I have a set of data that shows me a bunch of Fiscal Year.

I need to sum some data based on the current fiscal year and display 0 if that fiscal year field does not exist in the column.

e.g., I have already summed up the third field, and in the second field, we only see Q1 and Q2, I need Q3, and Q4 there as well. 

I would like it to say

[blank]    FY20-Q3      0

(and so on)

I will eventually have to take the fields in Fiscal Year column and convert those in to separate columns (which I will figure out how to do later)

I just need to know how to stack any missing fiscal (I had to do the calculation to figure out which year we're in and which Qtr)

Sorry for the confusion as I'm not sure how to properly explain.

Thank you!

[fmvizcaino · Accepted solution]

Hi @hal_dal ,

Attached is an example showing how to do it. The idea is to create a database with all the possible values considering all portfolios dynamically with a text input filling the fiscal year data.

Let me know if this makes sense to you.

Best,

Fernando Vizcaino

[hal_dal]

Hi @fmvizcaino 

Thanks so much for the solution! Its definitely what I'm looking for and makes a lot of sense.

The only problem I'm facing is with the second input you have added. Because these fields for me will be dynamic depending on which year we are in, I'm not sure how I could generate that from my side?

I see generate row tool but I don't think it does what I need it to do?

Thanks!

[danilang]

Hi @hal_dal 

Here's a simple modification of @fmvizcaino's solution that updates the fyx-q1 strings with the current year

The formula is 

Replace([Fiscal yr-qtr],"20",substring(DateTimeToday(),2,2))

which replaces the "20" with the last 2 digits of the current year.

Dan

[DavidP · Accepted solution]

If you have the year in your data, you can generate the year+quarters like this, and then use @fmvizcaino' s solution

[hal_dal]

@DavidP thank you so much! I don't have the date field but I was thinking of adding that based on today's date and just take the year and follow your steps as well as @fmvizcaino 's steps .

I appreciate all.of your suggestions and help! @DavidP @danilang @fmvizcaino