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 CodeLocation Count123456112345711234581123456012345601234591 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.

Tag Only First Instance Of a Value as 1

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.

Transformation

Accepted answers

DataNath

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

First occurence of location code.yxmd

All comments

DataNath

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

First occurence of location code.yxmd

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

Groups.yxmd

binu_acs

@biggdawg320 one way of dong this

tag_first_instance_09_05.yxmd

biggdawg320

@DataNath @IraWatt @binu_acs

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 . @DataNath, good spot though I'd definitely say both yours and @binu_acs, are simpler! Can just stick a record ID and sort on yours and beets mine all round :P

biggdawg320

@IraWatt @DataNath @binu_acs 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!

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167