nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Compare two tables and generate one piechart

duchangtuan

<p>Hello everyone. I have a question. There are two tables in my database. They are totally same in the table structure. Suppose table A and B. They have two field, the name is c and d. I want to get the number of A.c>B.c, A.c<B.c and A.c=B.c. Then generate a piechart that display the better, worse, same percentage.</p>

Find more posts tagged with

Comments

JFreeman

<p>You could join the two tables and then use computed columns and aggregations to get the values you want to use for comparison. You could then use that as the data for the chart.</p>

duchangtuan

<blockquote class="ipsBlockquote" data-author="JFreeman" data-cid="139724" data-time="1444927893">
<div>
<p>You could join the two tables and then use computed columns and aggregations to get the values you want to use for comparison. You could then use that as the data for the chart.</p>
</div>
</blockquote>
<p>Hello JFreeman. I am very glad you answered my post. I want to tell you more details about the question I encountered. I have two tables A and B. They are totally structure same, due to some reason we don't want to join them together. In order to simplify the complexity, suppose there are only two fileds in the table, ther name are a and b. I want to get the all the numbers that in the table A the a is same with the Table B's a, but the b is less, more, same with the table B's. Then we draw a piechart to display the result. </p>
<p>This is the query I used.</p>
<div>select v1.a, v2.a from A as v1 JOIN B as v2 ON v1.a= v2.a where v1.b< v2.b</div>
<div>But from the above query and aggregation, I can only get one number, how could I get the other two numbers? </div>
<div> </div>
<div>Another method I want to try:</div>
<div>I modify the query to:</div>
<div>select v1.a as v1a, v2.a as v2a from A as v1 JOIN B as v2 ON v1.a= v2.a</div>
<div>then in the computed columns, I add something in the Expression:</div>
<div>
<div>if ( row["v1a"]> row["v2a"]) {</div>
<div>"Better"</div>
<div>} else if ( row["v1a"] == row["v2a"] ){</div>
<div>"Same"</div>
<div>} else if ( row["v1a"] < row["v2a"] ){</div>
<div>"Worse"</div>
<div>}</div>
<div>How can i get the count number of "Better", "Same" and "Worse" respectively?</div>
</div>
<div>Hope your reply, thanks.</div>

JFreeman

<p>If you use that computed column as the source for the pie chart, I believe you should be able to set the aggregation to count in the chart builder.</p>