community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

Data Preparation

Highlighted
Asteroid

Hello Everyone,

 

I need suggestions on a data preparation task that I am working on.

 

The dataset has identical values for few columns for multiple records. I need to modify this dataset in 3 ways depending on these columns

 

1st way - Identify records which have similar values for the columns and then based on these values check if a value in a numerical column for any of these records is lesser than a fixed value,  If one of the records satisfies this condition, then all the other records which have similar values for these columns should also be marked with the same flag as the record which satisfies the condition.

 

2nd way - Same as 1st way + 1 more condition. Check if there are 3 records in this dataset which also satisfy an additional condition - The sum total of the value in the numerical column for these 3 records should be less than a certain value. If this condition is satisfied, then mark all the records with the same flag.

 

3rd way - Same as 1st way + 1 more condition. Check if there are 3 records in this dataset which also satisfy an additional condition - The sum total of the value in the numerical column for these 3 records should be less than a certain value and also the sum total of any of the 2 records from these 3 records should be more than 15% of the sum of the values in these 3 records.

 

It would be really helpful if anybody could take a look these cases and provide few suggestions on these scenarios.

 

Regards,

Ashish

Alteryx Certified Partner

I would start with Sort + Transpose/Crosstab + Multirow formula.

 

 

Asteroid

Hi gnans19,

 

Thanks for a prompt response!!

 

I have used Sort to arrange the dataset in the descending order of the columns's value that is required for comparisons. However, the number of records that I want to check would vary with the combination of the columns.

 

How do I use a multi-row formula here?

 

Any sample workflows? 

 

Regards,

Ashish Bhavnani

Alteryx Certified Partner

If possible, can you provide a sample dataset?

Asteroid
DepartmentProjectPeriodMeasureCurrent Value
FinanceProject12017Q1Automation7685904093
FinanceProject12017Q1Automation3739009242
FinanceProject12017Q1Automation1344204944
FinanceProject12017Q1Automation1191106798
FinanceProject12017Q1Automation433979129.5
FinanceProject12017Q1Automation369956352.4
FinanceProject12017Q1Automation280297113.2
FinanceProject12017Q1Automation197907823.3
FinanceProject12017Q1Automation185034863.1
FinanceProject12017Q1Automation122366794.9
FinanceProject12017Q1Automation79475352.45
FinanceProject12017Q1Automation74304411.09
FinanceProject12017Q1Automation38405927.3
FinanceProject12017Q1Automation29503374.67

 

Pasted above is a sample dataset that I have created. The first four columns contain identical values across the records and the 5th column contains a numerical value which has to be used for comparisons.

 

 

Quasar
Quasar

How about something like the attached? I also added in a check to verify that there were at least 3 records.

 

3 ways.png

Labels