Extract data, create a table & summarise in texts with ref data from table

[jcsoh]

Hi all,

How do I (i) extract data from Table A for a specific month only; (ii) create an output like Table B (that is in thousands); and (iii) create a summary of texts with references from Table B?

Table A

Month	Alpha	Bravo	Charlie	Delta	Echo	Foxtrot	Golf	Hotel
Jan	500	1010	1020	1030	2000	1050	1060	1070
Feb	1000	2010	2020	2030	3000	2050	2060	2070
Mar	1500	3010	3020	3030	4000	3050	3060	3070

Table B

Jan	Total
Bravo	1K
Charlie	1K
Delta	1K
Alpha	0.5K
Foxtrot	1.1K
Golf	1.1K
Hotel	1.1K
Echo	2K

Summary of texts with reference data point from Table B (bold blue texts are auto populated based on data from Table B)

It is in "Jan",where there is an estimated "10" [Charlie's total - Bravo's total] more than "1020" [Charlie]. It is "940" [Golf - Echo].

Alpha: 0.5K [Alpha's total]

Charlie: 1K [Charlie's total]

Echo: 2K [Bravo's total]

Cheers,

J

[AngelosPachis]

Hi J,

I think I can answer (i) and (ii) with the attached workflow. You can make it dynamic (as in allow the user to select which month he wants to filter on) by converting the workflow into an App. As for (iii) I have tried to answer that but I am not sure if that's exactly what you were after (note that Golf - Echo is negative) :

Please let me know.

Cheers

[jcsoh]

Hi @AngelosPachis, many thanks for your workflow. It works for (i) and (ii).

For (iii), is it possible in Alteryx to have specific texts that automatically state "more" or "less" depending on the output of "Charlie's total - Bravo total"?

For example if Charlie is 1020, Bravo is 1010, a statement will read like:

"It is in Jan, where Charlie is an estimated 10 more than Bravo."

For example if Charlie is 1010, Bravo is 1020, a statement will read like:

"It is in Jan,where Charlie is an estimated 10 less than Bravo."

Where can I learn about "Report Text"? Do you know any resources on this aside from the Alteryx own tutorial. Thank you.

Cheers,

J

[AngelosPachis · Accepted solution]

Hi J,

I have updated the workflow to address the new requirements. I just added a logical check (if Charlie>Bravo then more, else less)and then converted Charlie-Bravo into an absolute value

At the moment note that the Golf-Echo field isn't used anywhere, but I think you can amend the report text tool according to your preference. I'm unaware of any extra resources about the Report text tool aside from Alteryx tutorial trial to be honest; trial and error is the best practice and if you have any questions you can always post them in the community 🙂

Cheers

[jcsoh]

Hi @AngelosPachis, many thanks for your solutions. It works perfectly.

I have last few questions:

(1) How do I configure Table B to change the unit to either X.XM or X.XK depending on the value of the data? For example, if Alpha has a value of 10,500,400, the "Total" column will show a value of "10.5m". If it has value of 500000, then it will show a value of "500K". See Table C example below.

(2) How do I change the description under "Jan" column to "Negative" if the value in "Total" is below 0 and with red coloured text and "Positive" if the value in "Total" is above 0, and add a percentage to show the percentage decrease/ increase. See Table C example below.

(3) How do I create a merged cell header in between the data? See Table C example below.

(3) Would the #1 and #2 be also possible for the "Report Text" texts?

Table B

Table C where the value of Negative is [Alpha - Foxtrot]; Alpha is 10,500,400, Foxtrot is 11,000,000

Jan	Total
Alpha	10.5M
Bravo	1K
Charlie	1K
Negative	-500K (-4.5%)
Header
Echo	1.1K
Foxtrot	11M
Golf	1.1K
Hotel	2K

Cheers,

J

[AngelosPachis]

Hi J,

I'm glad you found my suggestion useful and thank you for marking my response as a solution.

For the next questions, I might need some extra help:

1) I configured the formula tool to change the units to X.X M or X.X K based on the data value and also address for the existence or not of a minus (-) sign (see attached workflow).

2) I have changed the text in "Total" to turn red if data is negative. I have done that with a column rule within the Report Table tool (see attached workflow). However, I don't understand why you would want to change the description under "Jan" column. Wouldn't you then lose the information Alpha, Bravo, Charlie offer? Why don't you create another column flagging if data under total is Positive or Negative?

Also, you can estimate a percentage change but compared to what? What drives you compare Alpha with Foxtrot and not Alpha with Echo for example? We cannot reach an accurate solution without that info.

3) By merging do you mean making the header "Header" span across both columns?

4) #1 can be done via using a Multi-Field formula tool which converts your numeric fields to strings and then brings them to the desired format (see attached workflow). I didn't get which part of #2 you want to implement in your report Text. Is it when a figure is negative to turn the text red?

Cheers

[jcsoh]

Thanks @AngelosPachis. Your inputs have been extremely useful for me to incorporate into my Alteryx workflows.

#2:

Regarding why I would want to change the description under "Jan" column, my apologies that I wasn't clear. 

The reason why I wanted to do that is because in future, there may be some additional hidden dataset from the original source that I would want to reveal and include into the Basic Table, for e.g. in my latest example, it's Alpha-Foxtrot. So instead of naming it Alpha-Foxtrot under "Jan", I wanted it to be renamed as another name of "Positive" or "Negative" depending on the value. See Table A and B below.

So, additional column that tells whether it's positive or negative is not required in my case. It's more of the description that I wanted to know how to do it, because I could be change it in future where required to for example, deficit or excess, or shortfall or surplus.

For percentage change, I used Alpha-Foxtrot as an example and there's no driving factor why I chose Foxtrot over Echo or any other data. My set of examples that I listed are placeholder data. In a scenario where Alpha is year "2006", Foxtrot is year "2001", it would be like finding out the percentage change (increase / decrease) from 2001 to 2006.

I wanted to know / understand how to achieve that outcome like how it gets formulated - that's the only driving factor, with no other specific logic behind the choices of data.

Table A

Month	Alpha	Bravo	Charlie	Delta	Echo	Foxtrot	Golf	Hotel	Alpha-Foxtrot
Jan	10,500,400	1010	1020	1030	2000	11,000,000	1060	1070	-499600
Feb	1000	2010	2020	2030	3000	2050	2060	2070	-1050
Mar	1500	3010	3020	3030	4000	3050	3060	3070	-1550

Table B

Jan	Total
Alpha	10.5M
Bravo	1K
Charlie	1K
Negative	-500K (-4.5%)
Header
Echo	1.1K
Foxtrot	11M
Golf	1.1K
Hotel	2K

#3:

Regarding your question on column, yes, it is referring to making the "Header" spanned across two columns. Think the table from this post is not appearing like the two columns have been merged, even though I have merged it before I posted.

#4:

Regarding your question on which part of #2 I wanted to implement in my Report Text, I meant to turn the text red when figure is negative and it also shows percentage increase/ decrease.

Hope this clarifies. Once again, thanks a lot.

Regards,

J

[jcsoh]

Hi @AngelosPachis, would you be able to revise the workflow based on my latest above reply?

Please let me know if you have any questions. Thank you.

Regards

J

[AngelosPachis]

Hi J,

2) All queries you had about the % change and changing names to negative or positive can be solved via a formula tool. I am attaching a workflow that's contains an indicative solution, but I am not sure if it's the best way to go about it. However it returns you what you were looking for. I've flagged the new bits with a yellow comment box.

3) I have never merged cells in the Table tool, but I guess this post from @HenrietteHaigh  might help you

https://community.alteryx.com/t5/Alteryx-Designer/Merging-cells-with-the-Table-Tool/ta-p/110194

4) Report text tool cannot dynamically change the colour of the text unfortunately, as far as I am aware of.

Hope that helps