Replacing Unadjusted Values with Adjusted Values

[Rcoleman2585]

Hello,

I am currently importing data from two separate sources on a monthly basis, one includes unadjusted values and the other includes adjusted values. I'm trying to create a filter based on the Month column so that when a current month's adjusted values come in it automatically filters out/excludes the rows for the unadjusted values. Right now I have the data source linked to the adjusted values showing the month as 'X Adj' so that when say January adjusted values are posted the month for those shows as 'Jan Adj'.

I need some type of condition for formula for if the column month includes 'Jan Adj' then 'Jan' is automatically excluded, and the same the next month in February. What is the best way to do this? Thanks!

[Yoshiro_Fujimori]

@Rcoleman2585 ,

Rather than having "X Adj" values in "Month" column, it would be more convenient to have a separate column for a boolean flag of "adjusted".

That said, if the data is as you described, I would parse the "Month" column to get the same effect.

Then you sort the data by "Month" and then "Adj" column.

The last 1 row of each "Month" would be what you want to get.

Is this what you want to do?

[Rcoleman2585]

Thank you for the reply but there are thousands of rows and I can't simply exclude only the last one.

[Yoshiro_Fujimori]

If you can uniquely find the row with Unadjusted Values which was updated by a particular row with "Adjusted Values", there should be a set of fields.

Then you add the fields to the Sort tool and the Sample tool (Group by Column).

I hope it works.

If you can share a sample data, I may understand your case more precisely.

[Rcoleman2585]

Hello, it is similar to the data below... essentially various months, regions, customers, and product types. However, we will receive an adjusted number for each month and for every new adjusted month I need to exclude the unadjusted month. So in the dataset below I would need to include Jan Adj, Feb Adj, Mar Adj, and Apr as we haven't received April adjusted numbers yet. However, when we do receive April adjusted numbers I want to automatically exclude 'Apr' and then the same thing the next month. I don't want to use a select for 'row-1' because there are thousands of rows with various customers, products, etc. Thanks!!

[Yoshiro_Fujimori]

@Rcoleman2585 ,

The workflow in my previous post works as you described.

(Please see the "Case2" flow in the attachment.)

I have a question in your sample. It seems the rows for a month can have different Regions.

Do you really want to overwrite 55154 (Jan USA) with 5151(Jan Asia)?

Month	Customer	Region	Amount
Jan	John	USA	55154
Jan Adj	Ashley	Asia	5151

If you want treat data with different Region with the same Month separately, you may add Region in Sort tool and Sample tool.

[Rcoleman2585]

No I do not, I can try to use the second workflow that you have attached below.

[Rcoleman2585]

This still does not appear to be working, is there no other way that the formula can be done? The issue is there are many different region, customer, product type combinations so sometimes there are completely new groupings in a new month I can't just delete the last (N - 1).

Sorry but I am new to Alteryx, I will try to explain the formula again below:

IF column Month contains 'Jan Adj' then exclude rows with month 'Jan'

IF column Month contains 'Feb Adj' then exclude rows with month 'Jan' and 'Feb'

IF column Month contains 'Mar Adj' then exclude rows with month 'Jan'' and 'Feb' and 'Mar'

IF column Month contains 'Apr Adj' then exclude rows with month 'Jan' and 'Feb' and 'Mar' and 'Apr'

IF column Month contains 'May Adj' then exclude rows with month 'Jan' and 'Feb' and 'Mar' and 'Apr' and 'May'

and so on for the remainder of the year.

Basically once we get the adjusted figures for a month, we need to exclude all rows for that month and the ones prior, I hope this makes more sense. Thanks

[Yoshiro_Fujimori · Accepted solution]

If that is what you want, I would change the data format and filter as below.

1. add a new column "Last Update Month" (in DATE type) which contains the same date in all rows (You can do this by Formula tool).

2. change the Month column to DATE format.

3. add a new column "Adj" (in Boolearn type) and set 1 to the adjusted rows and 0 to the original rows.

4. with Filter tool, you can pick only the original rows ("Adj" = 0) and "Month" <= "Last Update Month"

Good luck.

[Rcoleman2585]

Thank you... I finally got it to work although the weird part is when I set the criteria for 'Month < Last Update Month' the February values were excluded even though 2023-02-01 is not less than 2023-02-01. So what I did was just add in a formula prior to the filter to remove a day from last update month as shown below. But thank you for your help, greatly appreciated!!!

Datetimeadd(datetimeparse([Last Update Month], "%Y-%m-%d"),-1,"days")