Count attributes in same column

Question & Answer
1

Please i need help
I have column in my data base which have some repeated attributes as column base i want to count number of repeated attributes in the same column
for example i have column name “Code” which contain many codes

Code Column Repeated code
GD122545 1
GD445412 2
GD147452 1
GD445412 2
GD021253 1
GD122540 2
GD122540 2

2

You can add a calculated field using the countOver function as follows:

countOver({Code}, [{Code}], PRE_AGG)

You can read more about level-aware aggregations such as countOver here Using Level-Aware Aggregations - Amazon QuickSight

3

thanks darcoli for fast reponse i tried countover function as you recommended but it gives me blank value in the new calculated field!! note: Code column is string type is this the problem?

4

for reference, this was solved here Count string item in same column

5

Thanks darcoli for your support :heart_eyes:

6

I have another issue if you can support me
i have 2 tables that i should connect together to get my analysis
first table is constructed according to each order “Order_id” column so each row has a unique Order_id number and other columns containe order details like revenue, refund etc…

the second table is constructed according to each order view “Views” column per time and date so each row represent view record details for each Order_id

when i connect 2 tables together the revenue, refund amount is multiplied and explored according to “Views” column in second table so the revenue and refund amount give errors data… do you have any idea to connect two tables without make error of Order_id data like revenue and refund amount…

For example in excel i can make sumif number of views for each Order_id without affecting first table “Main table”…

note" database is SQL and the developer can’t fix it from SQL tables

7

Capture
Capture1445×740 68.4 KB

8

Capture2
Capture21424×646 67.2 KB

9

If I understand correctly, the main issue here is that you have a dimension table that also contains aggregated data and then you are joining this with a metrics table. Each aggregated value will be repeated for each row in the metric table so the final sum of values will be the multiple of the original value by the number of rows in the second table.

Typically I would say you need to redeisgn the underlying table but since you mentioned that is not possible, you can use this workaround to get the correct number of views in the analysis:

Add a calculated field views_first in the analysis:

ifelse(updated_at = minOver(updated_at, [order_id], PRE_AGG), views, null)

This field will only be populated for the first row for each order_id in the second table

10

Hi darcoli.
can i ask for help please
Question 1
I have 2 tables connected together

aws
aws1450×830 83.1 KB

Table 1 include all store deals codes
Table 2 include all orders generated

2 tables is connected together through column Deal_Code_id
in my analysis phase i can only see deal codes that have orders generated in table 2. but i can’t see all deals created in Table 1 if there is no orders in Table 2… How can i display all Deal codes even there is no orders to these deals

11

Question 2
How can i display only top 10 values in the chart?

12

Question 1
You need to change that join from a Right join to a Left join to include all Deals even those without a matching order. You may also consider using a Full other join if you want to include non-matching rows from both tables

Question 2
You can use a top and bottom filter in the analysis Adding a text filter - Amazon QuickSight

13

Thanks darcoli you really are awesome member