Dynamic generate fields based on result of one field

[blin]

Hi,

I am new to Alteryx and wondering of I can generate an output file as showing below.

On the right side is the source data for item related data. All fields are identical, in different season.

I would like to set up a work flow that will:

1. combine the source files and output identical field side by side using the value of the season field as prefix to distinguish which file the data is from.
2. Can I also set up condition formatting to highlight difference. For example, same item# with different HTS# in different season or same HTS# with different Duty Rate in different Season.

[Greg_Murray · Accepted solution]

@blin 

Here you go! 

Greg

[Qiu]

@blin 
 A bit different approach.

For the coloration, I dont think it is possible with Alteryx, specially for two seperated files.

We may do something by Row Rule inside the Table tool.

[blin]

Hi Greg,

Thanks for the solution. The output is exactly what I was ask for!

I don't understand some detail though.

1. would you please explain the data type you set up? I am not sure if it is related to later how you use group by item# and Sum. I would never think of setting the data type the way you did. Is it going to work if all the data type is set to string except Duty rate (%). Please let me know if this is a critical step, can I get away using string?

2.  Space and # are replaced by _, this is not caused by the formula. How to fix this?

Thanks,

Yushiu

[blin]

Hi Qiu,

Thanks for reply.

Sorry I should have label the example data better. The input file you generated is actually my expected output.

===================================================================

In my screenshot, the data source is the two tables on the right side, with same fields/structures. They will be saved in different files as that's how I download the data.

I want to combine these two files and return the result of combined fields displayed side by side instead of merge all the value under same field and label the fields with the value of the season.

So in this case, Would it possible to apply some rules to highlight the difference?

Thanks,

Yushiu

[Greg_Murray · Accepted solution]

Hi @blin,

1. You can swap the the summarization option in the crosstab tool to 'first' if you have strings passing through it. It isn't actually summarizing anything, so it's not a huge concern.

2. You can use the dynamic rename to edit the headers of the output.

See attached.

[blin]

Hi Greg,

Thanks for the reply.

1. Is there a place explaining what Method for Aggregation Values do? I don't have formal data analysis training so most of the time I learn from seeing the result. In this case, it gives me 3 options, Concatenates/First and Last. I don't really see the difference if I select all or just use "First". In what scenario, different options would make difference?

2. Is there a place that references all expressions and gives an example of syntax?

3. I notice in the Join, there are two colors, black and orange. The ones in black text are the final result while the orange ones are the original. The Join is placed after transformation. Why Alteryx still identify both? Logically I wouldn't include the Orange ones at all. I also can't see the difference immediately if I deselected the orange color ones. why did you choose to select them for this exercise? How do I determine if include or not?

Thanks!

[Qiu · Accepted solution]

@blin 
So sorry. can not believe I missed the arrow diffrect either.

Try this one.

So far I can do to mark the entire row for the color. Not sure how to do by columns and rows exactly

[blin]

Hi Qiu,

Thanks for providing your solution!

I learned a lot in just one day.

I was wondering if there's a place that I can find all the references.

1. Is there a place explaining what Method for Aggregation Values do? I was wondering what's the difference if I choose Concatenates/First, Last or all. I don't really see the difference if I select all or just use "First". In what scenario, different options would make difference?

2. I see you use Formula in "Edit row rule", I just wonder how did you know what to use, is there documentation for that?

Thanks!

[Qiu · Accepted solution]

@blin 

By answering your question, I hope I can get the accept mark now, 😁

First, the Alteryx Build in sample is very informative.

1. Is there a place explaining what Method for Aggregation Values do? I was wondering what's the difference if I choose Concatenates/First, Last or all. I don't really see the difference if I select all or just use "First". In what scenario, different options would make difference?

Again the samples. Concatenates/First, Last will make difference as explained below. You can try to switch to different options to see the difference.

2. I see you use Formula in "Edit row rule", I just wonder how did you know what to use, is there documentation for that?

Sorry, but still the sample. When see the sample for Formula, there is a number ''1", so indicates you will feed the rule with Row Numbers.