Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

jcsoh
7 - Meteor

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

AlphaBravoCharlieDeltaEchoFoxtrotGolfHotel
Jan5001010102010302000105010601070
Feb10002010202020303000205020602070
Mar15003010302030304000305030603070

 

Table B

JanTotal
Bravo1K
Charlie1K
Delta1K
Alpha0.5K
  
Foxtrot1.1K
Golf1.1K
Hotel1.1K
Echo2K

 

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

8 REPLIES 8
AngelosPachis
16 - Nebula

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) :

 

Annotation 2020-07-06 170133.png

 

Please let me know.

 

Cheers

jcsoh
7 - Meteor

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
16 - Nebula

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

 

Annotation 2020-07-07 063927.png

 

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
7 - Meteor

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

jcsoh_0-1594235306394.png

 

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

JanTotal
Alpha10.5M
Bravo1K
Charlie1K
Negative-500K (-4.5%)

 

 
Header
Echo1.1K
Foxtrot11M
Golf1.1K
Hotel2K

 

Cheers,

J

 

AngelosPachis
16 - Nebula

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
7 - Meteor

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

AlphaBravoCharlieDeltaEchoFoxtrotGolfHotelAlpha-Foxtrot
Jan10,500,400101010201030200011,000,00010601070-499600
Feb10002010202020303000205020602070-1050
Mar1500301030203030400030503060

3070

-1550

 

Table B

JanTotal
Alpha10.5M
Bravo1K
Charlie1K

Negative

-500K (-4.5%)

 

 

Header

Echo1.1K
Foxtrot11M
Golf1.1K
Hotel2K

 

#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
7 - Meteor

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
16 - Nebula

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

 

 

Labels