Einstein Analytics Tips – Hiding columns in Tables Dynamically

If you have ever needed to create a table in Einstein, you’ll know that there are times that you have to do a calculation but only want to see a result. You’ll be pleased to know that we can statically hide those columns. On the other hand, you may have a large amount of data that you would like to show, and it would be nice to auto-hide columns given certain conditions (perhaps every value is 0 for example). I would like to take you on a tour of Einstein’s column hiding and finish off with an approach to dynamically change the columns displayed on your tables!

Hiding Columns Statically

Let me explain briefly what I mean by a statically hidden column. What this means is that the columns are hidden, and will never be visible to the user under any circumstance (unless it is edited of course). However, just because they are hidden, doesn’t mean they aren’t useful, as they can still be used in calculations. This is already available within Einstein.

Let’s say that we have 5 measures, 4 of which need to be added together, but we only want to see the result. The following screenshot should give a good example of what we are doing.

j1*Open the picture to see fullsize image*

As you can see, we created a new column using the formula B+C+D+E. Now let’s hide each of these columns while keeping the results shown. We can do that by hitting the arrow on the side of the measure and clicking “hide”.

j2

j3

As we can see, the calculations are working and the columns are hidden! Wonderful! Now, let’s try something a little more complex, changing what columns are displayed dynamically.

Hiding Columns Dynamically

To dynamically change the columns displayed, we’re going to something that some of you information security engineers will notice right away. We’re going to create a hidden SAQL step to inject new SAQL code into our query used to power the table. It sounds complicated, so we’ll take this slow.

First, let’s create the SAQL step that will display the results of our query on a compare table. Here is our query and the results:

q = load \”jdc_dataset2\”; q = group q by (‘Account’, ‘Opportunity’,’Industry’); q = foreach q generate ‘Account’ as ‘Account’, ‘Opportunity’ as ‘Opportunity’, ‘Industry’ as ‘Industry’, sum(‘value’) as ‘sum_value1’, sum(‘value2’) as ‘sum_value2’, sum(‘value3’) as ‘sum_value3’, sum(‘value4’) as ‘sum_value4’, sum(‘value5’) as ‘sum_value5’; q = order q by (‘Account’ asc, ‘Opportunity’ asc); q = limit q 200;

j4

Now, let’s create our hidden step. This is a step that we will not add to a widget and will be used to create our conditions and code to be injected. We will be using a few case statements to create our conditions:

q = load \”jdc_dataset2\”;q = group q by ‘all’; q = foreach q generate case when sum(‘value2’) > 0 then \”, sum(‘value2’) as ‘value2’\” else \”\” end as ‘dynamic_value_2’, case when sum(‘value3’) > 0 then \”, sum(‘value3’) as ‘value3’\” else \”\” end as ‘dynamic_value_3’, case when sum(‘value4’) > 0 then \”, sum(‘value4’) as ‘value4’\” else \”\” end as ‘dynamic_value_4’, case when sum(‘value5’) > 0 then \”, sum(‘value5’) as ‘value5’\” else \”\” end as ‘dynamic_value_5’;

If this were to be added to a table widget, this would be the results:

Now, for the last step, let’s add our results bindings to the first step. We will replace parts of our query with the binding:

q = load \”jdc_dataset2\”;\nq = group q by (‘Account’, ‘Opportunity’,’Industry’);\nq = foreach q generate ‘Account’ as ‘Account’, ‘Opportunity’ as ‘Opportunity’, ‘Industry’ as ‘Industry’, sum(‘value’) as ‘sum_value1’ {{cell(dynamic_columns_hidden.result,0,\”dynamic_value_2\”).asString()}}{{cell(dynamic_columns_hidden.result,0,\”dynamic_value_3\”).asString()}}{{cell(dynamic_columns_hidden.result,0,\”dynamic_value_4\”).asString()}}{{cell(dynamic_columns_hidden.result,0,\”dynamic_value_5\”).asString()}};\nq = order q by (‘Account’ asc, ‘Opportunity’ asc);\nq = limit q 200;

And that’s it! We can now use our list selectors to filter our results, and if the sum of a column is not greater than 0, then it will not be projected! Let’s see it in action!

 

Final Thoughts

Both of these ways of displaying columns are powerful in their own right, but dynamically changing columns opens you up to a huge amount of potential. Hopefully we have shown you a new world for viewing your data.