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 Cloud Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Cloud.
SOLVED

I have two duplicated values and I want to change just one, but I cannot find the right option...how can I do this?

Trifacta_Alumni
Alteryx Alumni (Retired)
 
6 REPLIES 6
Trifacta_Alumni
Alteryx Alumni (Retired)

Hello Jared, is there a pattern to the two duplicate values? Perhaps the values before and after these duplicate values could be used to distinguish them?

 

The Replace transform, which allows you to tailor the actions based based on what is being matched, would be a good place to start.

https://www.trifacta.com/support/articles/article/625245-replace-transform/

 

Trifacta_Alumni
Alteryx Alumni (Retired)

I looked through that article, but I'm still not quite sure what to do. The two rows that I want to fix each have the value "Active" in the "customer_status" column. But These rows represent the same customer, just for different subscription dates. I want to change "Active" to "Expired" for the older of the two subscriptions...the record with the oldest end date. I keep trying to make this change, but I can't figure out how to add the condition to the replace operation.

Trifacta_Alumni
Alteryx Alumni (Retired)

In this case, I would recommend that you use the customerID and dates as indicators for when to flip from "Active" to "subscribe" In this situation, a combination of windows transform and the PREV function would be more useful to you.

 

An example of the formula would be,

window value: if(UnixTime > prev(UnixTime, 1), 'subscribe', 'active') group: CustID order: subscription_date

 

Keys:

  • UnixTime = a new column generated to allow time to be calculated. It references to the subscription dates.
  • CustID = Customer ID
  • subscription_date = date subscribed to service

 

This function identifies the latest date between two entries and change the output text to "Subscribe."

 

Note: The name of the newly generated column would need to be renamed.

Trifacta_Alumni
Alteryx Alumni (Retired)

Here's the link to the documentation.

 

https://docs.trifacta.com/display/PE/PREV+Function

Trifacta_Alumni
Alteryx Alumni (Retired)

Perfect, thanks for clarifying! This makes sense now

Trifacta_Alumni
Alteryx Alumni (Retired)