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

Match values between two columns based on ID from another column

hal_dal
8 - Asteroid

Hi all,

 

I am trying to figure something out and after nemerous searches and i still cannot find a post that does what I'm looking for.

 

I basically have an ID(a) field, another field with Date(b) and a third field that I did the calculations for which gives me the Date(c) as well.

I need to figure out whether value from c matches b, based on the ID in a

 

e.g., (calculating sum based on year)

 

IDDate-CurrentDate-PreviousPriceSum-CurrentSum-Previous
123FY19FY181001000
123FY19FY181001000
678FY18FY1750500
456FY19FY181001000
456FY20FY19150125125
456FY20FY19100125125

 

I basically do calculation for Date Previous which should give me the previous year

 

 

Sorry if this is confusing, all I need to do is calculate the "Sum-Previous" field, but only sum the Price field if value of Date-Previous exists in Date Current based on the ID.

 

Please let me know if I should specify anything else.

 

 

Thank you

8 REPLIES 8
fmvizcaino
17 - Castor
17 - Castor

Hi @hal_dal ,

 

I'm attaching an example that shows how to do it.

 

Take a look and let me know if this works for you.

Best,

Fernando Vizcaino

hal_dal
8 - Asteroid

Thank you so much!

That's a very clever way of dealing with this.

It seems to work for the data set whose previous date doesn't already exist.

But for any previous date that exist in the current date field, it's not grabbing the values of the "current date" price 

 

 

You will see below that the first 3 rows in the last column is showing 0 which is correct since the Previous Date doesn't exist in Current Date.

But anything after that, the previous date do exist, but instead of showing 76, as that's the relevant data, it's showing 831

 

hal_dal_0-1581714930162.png

 

hal_dal
8 - Asteroid

. duplicate

fmvizcaino
17 - Castor
17 - Castor

Hi @hal_dal ,

 

Would you be able to share that dataset for me to adapt to it?

 

Best,

Fernando V.

hal_dal
8 - Asteroid

@fmvizcaino ,

 

Here is my attachment, I have removed some irrelevant data and have made them short.

Thank you so much for your patience and for answering me so quickly!

 

 

hal_dal
8 - Asteroid

@fmvizcaino ,

 

Just wondering if you were able to see what I may have done wrong? 🙂

I have been trying a similar logic with different tools/columns but no luck.

 

Thanks!

fmvizcaino
17 - Castor
17 - Castor

Hi @hal_dal ,

 

Sorry, I've forgot to check it out due to the weekend.

I think I've misunderstood the proposal at first, but I've corrected now.

 

Best,

FErnando Vizcaino

hal_dal
8 - Asteroid

Thank you sooo much!!! Worked like a charm!

Labels