Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cengizeralp
Contributor III
Contributor III

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.

Tables.JPG.

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?

Thanks in advance.

1 Solution

Accepted Solutions
Digvijay_Singh

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

ServiceName:

Load Distinct Assembly_Service_Name as Service_Name

Resident Assemblies;

Concatenate

Load Distinct Repair_Service_Name as Service_Name

Resident Repairs Where Not exists(Service_Name,Repair_Service_Name);

Capture.PNG

View solution in original post

13 Replies
zebhashmi
Specialist
Specialist

can you share some inline data sample?

cengizeralp
Contributor III
Contributor III
Author

I made the table names english for better understanding.


Assemblies:

Load * Inline

[SerialNo, Assembly_Service_Name

1, A

2, A

3, B

4, B

5, B

6, A

7, A

8, B

9, B];


Repairs:

Load * Inline

[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

service.JPG

I hope I could explain the situation

zebhashmi
Specialist
Specialist

Do you want this in KPI or in Table?

cengizeralp
Contributor III
Contributor III
Author

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

Map will show the ratios by the service company name.

zebhashmi
Specialist
Specialist

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)

cengizeralp
Contributor III
Contributor III
Author

Hi Jahanzeb,

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

zebhashmi
Specialist
Specialist

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)

Digvijay_Singh

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

Assemblies:


Load SerialNo&'-'&Assembly_Service_Name as Key,

'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:

Load SerialNo&'-'&Repair_Service_Name as Key,

    'R' as RType

    Inline

[SerialNo, Repair_Service_Name

1, A

2, B

3, A

4, A

5, B];

Capture.PNG

cengizeralp
Contributor III
Contributor III
Author

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.