topic Re: Data Model producing multiple records in QlikView

Data Model producing multiple records

cbaqir — Wed, 10 Nov 2021 14:03:02 GMT

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?

Re: Data Model producing multiple records

abhijitnalekar — Wed, 10 Nov 2021 03:41:44 GMT

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.

Re: Data Model producing multiple records

cbaqir — Wed, 10 Nov 2021 14:02:36 GMT

Sample file attached, thanks

Re: Data Model producing multiple records

cbaqir — Thu, 11 Nov 2021 14:20:26 GMT

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

Re: Data Model producing multiple records

MarcoWedel — Thu, 11 Nov 2021 17:34:16 GMT

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.

hope this helps

Marco

Re: Data Model producing multiple records

cbaqir — Thu, 11 Nov 2021 17:36:14 GMT

Thank you! I will look into this.

Re: Data Model producing multiple records

cbaqir — Thu, 11 Nov 2021 18:27:06 GMT

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;

Re: Data Model producing multiple records

MarcoWedel — Thu, 11 Nov 2021 21:29:42 GMT

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