Tag Only First Instance Of a Value as 1

[biggdawg320]

Hi,

    I have a dataset with location code values that may or may not show twice.

    I would like the first instance of the location code value to show as 1, and all other instances of this location code to show as 0.

Example of Desired Output:

Location Code	Location Count
123456	1
123457	1
123458	1
123456	0
123456	0
123459	1

How would I go about doing this in Alteryx?

In Excel I would simply make a column with the formula: "= IF (COUNTIF ($G$2:$G2,G2) > 1 , 0 , 1 )".

Then as it's autofilled down it would only return a 1 if the count is for the first time the value shows, any other count would return a 0.

[DataNath · Accepted solution]

Would this do the trick? If not let me know with extra conditions and I'll have another crack.

[IraWatt]

Hey @biggdawg320,

There's probably loads of ways of doing this in Alteryx. First way which comes to mind for me is using the sample tool. The sample tool can grab the first element in a group and then we use the formula to give it the value of 1. On the other side we skip the first element for each group and assign them zero. Then union everything back together

HTH,

Ira

[binuacs]

@biggdawg320 one way of dong this

[biggdawg320]

@DataNath @IraWatt @binuacs 

Thanks all for such a quick response.

I will try these when I'm back to working on it (meetings amIright? 😁).

I'm about 2 weeks into Alteryx Designer and coming from 16 years of Excel work I am still trying to refit my thinking to this method.

Wish me luck.

[DataNath]

Awesome, let us know how you get on! That's great to hear - I'm sure you'll absolutely love finding all the ways that Alteryx can make your life easier then!

In terms of the solutions here, all 3 achieve the same result. However, if you're precious about the sort of the location code then @IraWatt's method maintains this!

[IraWatt]

lol best of luck @biggdawg320 :D. @DataNath, good spot though I'd definitely say both yours and @binuacs, are simpler! Can just stick a record ID and sort on yours and beets mine all round :P 

[biggdawg320]

@IraWatt @DataNath @binuacs Thanks again for your help.

I ended up implementing @DataNath version of the fix and I found it worked well and efficiently.

[DataNath]

Anytime! If things are solved then feel free to mark the solution, otherwise let us know how else we can help!