Data Model producing multiple records

[cbaqir]

I am trying to do a sum of a field in the EHRG_TICKET table called TOT_EST_PROD_BUILD_HRS. If I have one Ticket selected like 72, it is multiplying it x times (in this case 10). 

=sum(TOT_EST_PROD_BUILD_HRS) = 110

but it should be only 11

My app is returning multiple rows when the source table has only one. I believe it is a data model issue but I do not know how to fix it. None of thee joined tables returns 10 rows so I am not sure where the issue is.

EHRG_TICKET is the main table and has one row per DEFECT_ID in the database. However, I can see multiple rows in the preview and I believe this is due to the joins to the other table. How can I fix it when I still need the other tables?

[abhijitnalekar]

Hi @cbaqir,

I can sense that there is one table in the model which makes the many to many join result into multiple lines in the data.

Is it possible to share the sample data for each table to check at my side.

[cbaqir]

Sample file attached, thanks

[cbaqir]

I cannot figure out where the issue is. Any ideas?

[MarcoWedel · Accepted solution]

Hi,

these two lines of your script multiply the records of your EHRG_TICKET table:

subfield(BUILD_RESOURCES,';') as BUILD_RESOURCES_SUB,
SubField("DESIGN_GROUPS",';') as DESIGN_GROUPS_SUB,

In your example they generate 5 x 2 = 10 multiples of the row DEFECT_ID=72 having TOT_EST_PROD_BUILD_HRS =11, so 

sum( TOT_EST_PROD_BUILD_HRS)

will return 10 x 11 = 110 instead of just 11.

Try extracting the subfields in separate tables instead in subsequent resident loads.

See also: https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/StringF...

hope this helps

Marco

[cbaqir]

Thank you! I will look into this.

[cbaqir]

Do I need multiple Resident loads?

If I run this, I lose all of the other fields from the Resident table I need.

SepNames: //Needed to prevent duplicate records CB 11/11/21

Load DEFECT_ID,TAGS_GOALS,DEFINE_BODIES,DESIGN_GROUPS,BUILD_RESOURCES,TAGS_TICKET_TRACKING,NAT_CLINIC_INFORM,

subfield("TAGS_GOALS",';') as TAGS_GOALS_SUB,

SubField("DEFINE_BODIES",';') as DEFINE_BODIES_SUB, //CB 6/17/20
SubField("DESIGN_GROUPS",';') as DESIGN_GROUPS_SUB,//CB 6/17/20
subfield(BUILD_RESOURCES,';') as BUILD_RESOURCES_SUB,
subfield("TAGS_TICKET_TRACKING",';') as TAGS_TICKET_TRACKING_SUB,
subfield("NAT_CLINIC_INFORM",';') as NAT_CLINIC_INFORM_SUB

Resident RPT_EHRG_TICKET;
Drop Table RPT_EHRG_TICKET;

[MarcoWedel · Accepted solution]

Just delete the subfield() rows from your EHRG_TICKET load and afterwards create the "Sub" fields in additional tables associated with the EHRG_TICKET table.

For the examples of "BUILD_RESOURCES_SUB" and "DESIGN_GROUPS_SUB" this could look like this:

tabBuildResourcesSub:
LOAD Distinct
     BUILD_RESOURCES,
     SubField(BUILD_RESOURCES,';') as BUILD_RESOURCES_SUB
Resident EHRG_TICKET;

tabDesignGroupsSub:
LOAD Distinct
     DESIGN_GROUPS,
     SubField(DESIGN_GROUPS,';') as DESIGN_GROUPS_SUB
Resident EHRG_TICKET;

hope this helps

Marco