Interaction between two tables

[chaudharydeepak]

Table1:

unique_id    Description

1                    Fruits

2                    Fruits

3                    Food

4                    Furniture

Table2:

unique_id       Amt

1                    5

2                    10

3                    50

4                    30

Create Bar chart from table 1 and Key value from table 2

Bar chart – Description wise unique records

Key value – Total amount

Try to see if we can create interactions between these two tables.

[PaigeMiller]

I feel this is a very unclear set of instructions. Can you explain further?

[chaudharydeepak]

@PaigeMiller  Hello;

There are two different tables loaded in the cas server, and the client did not want to join the table. , and they want to create two different charts and want to give the intersection between those two charts.

[PaigeMiller]

---

@chaudharydeepak wrote:

There are two different tables loaded in the cas server, and the client did not want to join the table. , and they want to create two different charts and want to give the intersection between those two charts.

---

Intersection or interaction? These are not the same.

"Intersection between charts" is not a meaningful concept in my mind.

If we can't merge the tables (why not?), then how can we proceed?

The whole thing doesn't make sense to me, and instead of these very brief instructions and very brief explanation, perhaps you could give a more detailed set of instructions and a more detailed explanation.

[chaudharydeepak]

@PaigeMiller - Below is the detailed view on the query:

One of our client is working on SAS VA (on Viya) and they have multiple tables which they are using for their report development and multiple reports are prepared from respective tables.

For few reports, they wanted to have interactions between two charts which are created from 2 different tables. The challenge is that all the tables are there in CAS (in-memory) and the client don't want to go back to data preparation to join these tables for these specific reports, as they have limited memory in RAM and few of these table heavy (in terms of size).

Now, below is an illustrative example which I have shared for a sample data:  

Table1 has a unique id with description: 

Unique_id Description

1               Fruits

2               Fruits

3               Food

4               Furniture

Table2 has a unique id with amount: 

Unique_id amount

1               5

2               10

3               50

4               30

If we create a simple bar chart based on Table 1 with description and unique id and second chart  as key value with total amount based on table 2.

Now, we want create an interaction between bar chart and key value. Currently, with SAS VA (on Viya) we are not able to make interaction as both table doesn't have the common value present in the bar chart. 

Can it be possible to create such kind of interactions in SAS VA (on Viya) or SAS VA 7.5 where we do not need to join multiple data table but the chart interactions can happen on the fly using the common unique id present in both tables but common unique id is not shown in any chart or table.

[PaigeMiller]

Thank you for the explanation. However, since I don't use SAS VIYA or VA, I cannot help further. I'm sure others can assist you.

[Kurt_Bremser]

Sorting and joining depends purely on disk space, so RAM is not a showstopper.

If the performance of SQL is a problem, doing two sorts and joining in a data step is just a little more typing, but that's all.

proc sql also runs in CAS, so you should be able to use my SQL in Viya.

[chaudharydeepak]

@Kurt_Bremser  - I understand the Proc SQL and Dataset is the prime solution to this problem, which we also proposed to client.

But client do not want to write any data step or proc sql query for these reports. As some of these report would self-service reports where business users will login in SAS VA and develop reports on the fly and most of these business users would be non-technical resources who can work only using simple drag and drop features. 

In such situation, we cannot expect them to write code to create another dataset for the same and then use the same in respective report. What we are looking for is a possible alternative solution where we donot have to write any SQL or data step statement and we can directly create interactions in SAS VA. 

[Kurt_Bremser]

Do you mean this join?

proc sql;
create table want as
select
  a.description,
  sum(b.amt) as amt
from table1 a inner join table2 b on a.unique_id = b.unique_id
group by a.description;
quit;

and then point-and-click a bar chart from want.

[Reeza]

What you're trying to do is dynamic interactions on a graph based on another graph, or a report interaction. If the data source is the same, this is easy, if they're different you do have to complete some extra steps but it should be possible.

Not sure which version of VA you're using:
http://support.sas.com/documentation/cdl/en/vaug/69957/HTML/default/viewer.htm#p1bki2z2jxzsoen15hug3...

You should post these questions in SAS VA, otherwise you'll get programming solutions.

[Reeza]

I've moved your post to the SAS VA forum.

[GertNissen]

Hi @chaudharydeepak 

This respons is valid in VA 7.3 (You have not stated what version of VA you are using - would be helpfull to know)

1) Make sure your Unique_id is a category

2) Make sure that it's also been used in both visualizations

Btw: You could remove Unique from the List Table object (not needed, just added it for clarity)

Next you add the interaction (remember it will only work on visible columns in Object 1 - here the Bar Chart)

Select Unique_id from both tables.