The "append fields" with unique id

[chuntingsimonlo]

I am studying my teammate alteryx program, there have two tables
one is look like this:

id	amount	count
1	123	1
2	567	1
2	321	1

and he just selected the unique id, then the table become like this:

id

1

2

and one is look like this:

count	viollation
2	Y
2	N

After he use the "append fields" to combine two table, the output should be like this right:

id	count	viollation
1	2	Y
1	2	N
2	2	Y
2	2	N

But don't know why, the output is look like this: 

id	count	viollation
1	2	Y
1	2	N

Can anyone tell me whats going on? Thanks.

[JosephSerpis]

Hi @chuntingsimonlo when I use the append tool I get the output you describe.

I only get the output you describe by using a join tool with the configuration on postion.

[FinnCharlton]

After a quick test this works as expected for me, can you provide some more details about what is going on?

[chuntingsimonlo]

Thanks for your reply,
i know that if using "join" the output table would be like my describe.
but it is using the 'append fields', that's why i want to know why.

[ShankerV]

Hi @chuntingsimonlo 

The append fields tool works as expected.

To investigate more on this, can you please share some screenshots or the worflow.

But don't know why, the output is look like this: 

id	count	viollation
1	2	Y
1	2	N

Many thanks

Shanker V

[chuntingsimonlo]

Thanks for you guys reply,
i am sorry about i cannot show you guys the result, because the company policy.

Many thanks
Simon 

[ShankerV]

Hi @chuntingsimonlo 

Here in the highlighted in Yellow, you have used IF statement.

It stores Y or N only, hence it is only 1 record created.

[ShankerV]

Hi @chuntingsimonlo 

If count is >0, then it shows the value as Y which is Byte data stores as 1.

So the value stored in BFL_Violation is only Y or N, that is only 1 value at a time.

Hence you get the output. i.e the value out of IF statement stored as 1 gets append with the other two unique values.

id	count	viollation
1	2	Y
1	2	N

Many thanks

Shanker V

[chuntingsimonlo]

Thanks, can i know more about that how alteryx to join them, because there should be no id as the joining key

[ShankerV]

Hi @chuntingsimonlo 

As we have only one output, how you need the expected output.

Based on that can suggest the best possible way to join.

Many thanks

Shanker V