# LAC help with deduplicated tables

I have two tables on the same dataset:

Table 1
project item id cost
A monitors 100
A keyboards 50
A keyboards 50
A mouse 20
B desk 200
B desk 200
B keyboards 2 50
B mouse 2 20
Table 2
Project member id cost
A 1 1000
A 1 1000
A 2 2000
A 3 3000
B 4 1000
B 5 2000
B 5 2000
B 6 3000

I want to get the ratio of cost per project in table 1 to that of table 2. (total cost for A in table 1/ total cost for A in table 2) However, first I need to duplicate my rows. Iâ€™m doing this by getting the cost and using the IDs as follows:

for table 1:
min(cost, [item id])

for table 2:
min(cost, [member id])

once deduplicated, I need to add all the rows per project and then divide them by one another.
I am not sure how the syntax for this LAC would work. Any help?

Thanks!

Do you mean (total cost for A in table 1)/(total cost for A in table 2)?

1 Like

yes, sorry thatâ€™s exactly what I meant!

I could be wrong but I donâ€™t think you can use LAC-A here. I would use LAC-W instead.

Table1:
Cost per project = sumOver(cost / countOver({item id}, [{project}, {item id}], PRE_AGG), [project], PRE_AGG)

Table2:
Cost per project = sumOver(cost / countOver({member id}, [{project}, {member id}], PRE_AGG), [project], PRE_AGG)

Then divide the first cost by the second one.

1 Like

The countOver counts how many times each item id or member id appears for the same project. When you divide the cost by countOver, youâ€™re effectively removing the duplicates. For example, you donâ€™t want the cost for project A in table 1 to be 100 + 50 + 50 + 20 because â€śkeyboardsâ€ť will be counted twice. Dividing by countOver changes the calculation to 100 + 25 + 25 + 20.

1 Like

This worked perfectly. Do you have any resources other than QS documentation that might help with complicated calculations like this?

The only video where Iâ€™ve seen this technique used to handle duplicates is this one:

Itâ€™s a long video and youâ€™ll have to find where exactly in the video itâ€™s covered. Although itâ€™s an older video, I would highly recommend watching it because it covers more advanced problems. In some cases you can use LAC-A to handle duplicates but I find that LAC-W is still needed sometimes.

Other than that, Iâ€™m working on writing up some tips to share with the community. I find that some of the most common questions in the community are related to LAC. Iâ€™m hoping the tips will help better understand when to use LAC-A versus LAC-W, and how to handle mismatched aggregation errors.

2 Likes

Thanks for sharing this video (and how the content may be useful to others) @David_Wong!