topic Re: Getting rid of loops in QlikView

Edit script to create new field

cbaqir — Fri, 15 Nov 2024 23:47:58 GMT

I am adding in a couple of new tables and I am stuck on the scripting because of loops and at a loss for how to fix it. I have just added the RPT_EHRG_BUILD_HOURS, GovernanceCheckbook and xwalk tables. The problem is that the xwalk is needed because some of the Governing Groups in the Governance Checkbook table are not a 1:1 match for the PRIMARY_DEFINE_BODY in the Fact table called EHRG_TICKET.

MY QUESTION: How can I edit the script to use the PRIMARY_DEFINE_BODY from the EHRG_TICKET table to do some logic to determine the applicable GOVERNING_GROUP (from the xwalk) and then write it as a new field to the RPT_EHRG_BUILD_HOURS table?

left join (RPT_EHRG_BUILD_HOURS)
LOAD distinct (DEFECT_ID),[Governing Group]&'-'&monthstart(month) as %GG_MONTH_KEY
Resident EHRG_TICKET;

One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. Loop(s) will be cut by setting one or more tables as loosely coupled.

For example: ticket 814 should be Nursing Practice but the Governing Group in the top table shows No Define Needed, which isn't correct.

Re: Getting rid of loops

edwin — Fri, 16 Oct 2020 13:43:42 GMT

the obvious issue is with PRIMARY DEFINE BODY field. you need to make a decision if this is related to DEFECT ID or GOVERNING BODY. then remove it (or rename it) in the other table.

does it change with defect ID or will it change with governing body

Re: Getting rid of loops

cbaqir — Fri, 16 Oct 2020 14:10:55 GMT

Primary Define Body has a 1 to 1 relationship with Defect ID but Governing Body is the grouping for the available hours. It just doesn't relate 1 to 1.

Re: Getting rid of loops

cbaqir — Fri, 16 Oct 2020 16:37:55 GMT

I have an idea. If I can omit the xwalk and edit the script for the Build Hours table to add a field that shows the applicable Governing Body based on the Primary Define Body in the EHRG TICKET table, that might work. I am not sure how to do that.

Re: Edit script to create new field

edwin — Sat, 17 Oct 2020 02:04:36 GMT

Ideally, your data model should be a star. however, due to 1-to-many relationships, you may be forced to snow flake. if you can collapse the tables well and good. but that is not your problem. again your problem if PrimaryDefineBody is on two associated tables which causes the link. the first question to ask is should that field be associated to defect id or governing body? if it is associated with defect id then just drop it from xwalk. if for some reason you still need it (maybe you need to compare?), just rename it in xwalk. and you have a simple change (i think).

collapsing the tables in an existing data model would require more testing:

binary [gov checkbook grp.qvw]; NoConcatenate Newxwalk: load PRIMARY_DEFINE_BODY as PRIMARY_DEFINE_BODYFromxwalk, [Governing Group] Resident xwalk; drop table xwalk; exit script;

Re: Edit script to create new field

edwin — Sat, 17 Oct 2020 02:09:25 GMT

with the new DM:

Re: Edit script to create new field

Brett_Bleess — Tue, 27 Oct 2020 18:41:56 GMT

Following Design Blog should be of some help:

https://community.qlik.com/t5/Qlik-Design-Blog/Circular-References/ba-p/1469332

Cheers,
Brett