13 Replies Latest reply: Mar 9, 2018 2:55 PM by Cengiz Eralp

# counting similar fields in different tables

Hi experts,

I have a problem about calculation of a ratio for service units.

I have two tables, one for assembly of the products ("Izlenebilirlik") and one for service calls of these products ("Servis_Fişleri"). This two table is connected with product serial number as you can see from the picture below.

"ServisAdı_" and "Mon_Servisadı" comes from the same table and so has the same service company name dimension.

.

The trouble is that one product can be assembled and repaired (if there is) by different service companies. And I would like to find a process ratio for every service company without restriction of the relation.

Nominater = How many repair were done by service compnay A : Count(ServisAdı_)

Denominater= How many assembly were done by service compnay A :Count(Mon_Servisadı)

Can anyone help on this?

• ###### Re: counting similar fields in different tables

can you share some inline data sample?

• ###### Re: counting similar fields in different tables

I made the table names english for better understanding.

Assemblies:

[SerialNo, Assembly_Service_Name

1, A

2, A

3, B

4, B

5, B

6, A

7, A

8, B

9, B];

Repairs:

[SerialNo, Repair_Service_Name

1, A

2, B

3, A

4, A

5, B];

I want to find a ratio for service A = 3/4, and for service B = 2/5. But I can not find these values in the same table to find the ratios

I hope I could explain the situation

• ###### Re: counting similar fields in different tables

Do you want this in KPI or in Table?

• ###### Re: counting similar fields in different tables

I will use it in a table and later in a map.

Map will show the ratios by the service company name.

• ###### Re: counting similar fields in different tables

EDITED

would that work for you

=Count({<Repair_Service_Name={'A'}>}Repair_Service_Name)/Count({<Assembly_Service_Name={'A'}>}Assembly_Service_Name)

=Count({<Repair_Service_Name={'B'}>}Repair_Service_Name)/Count({<Assembly_Service_Name={'B'}>}Repair_Service_Name)

• ###### Re: counting similar fields in different tables

Hi Jahanzeb,

I have a lot of service companies and I need one expression to calculate the ratios for every company.

• ###### Re: counting similar fields in different tables

Ya I was thinking that, try to do this way

if you can add another field just like S.no

this will work with the selection of both repair and assy

Count({<Assembly_Service_Name=>}SerialNo)/Count({<Repair_Service_Name=>}new)

• ###### Re: counting similar fields in different tables

Hi Jahanzeb,

Firstly, I would like to thank you for your support.

I tried your method and I saw it worked like an "OR" command because of the relation.

While getting company related records in one table, it took the the other companies at the other table.

• ###### Re: counting similar fields in different tables

You may need some change in data model to achieve this, one way to do could be like this -

Assemblies:

'A' as AType

Inline

[SerialNo, Assembly_Service_Name

1, A

2, A

3, B

4, B

5, B

6, A

7, A

8, B

9, B];

Repairs:

'R' as RType

Inline

[SerialNo, Repair_Service_Name

1, A

2, B

3, A

4, A

5, B];

• ###### Re: counting similar fields in different tables

It solved my problem but changed completely my key field behaviour . The relation for many serial numbers have broken.

I am using this structure for many other purposes. So I need a solution without changing key field structure.

• ###### Re: counting similar fields in different tables

Thank you - I think you have to charge Your Data Modle, A in Assy and A in Repair are two different identities.

I tried Using P() but no we need some relation to that. Maybe some can help Thank you!

• ###### Re: counting similar fields in different tables

I think this is better, no change in existing model but you need one extra island table  as below -

ServiceName:

Resident Assemblies;

Concatenate