Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist
Specialist

Edit script to create new field

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.

2020-10-15_16-11-16.jpg

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

2020-10-15_16-16-56.jpg

Labels (1)
  • loops

6 Replies
edwin
Specialist III
Specialist III

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

cbaqir
Specialist
Specialist
Author

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. 

cbaqir
Specialist
Specialist
Author

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.

edwin
Specialist III
Specialist III

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:

 

 

edwin_0-1602900240721.png

binary [gov checkbook grp.qvw];

NoConcatenate
Newxwalk:
load PRIMARY_DEFINE_BODY as PRIMARY_DEFINE_BODYFromxwalk,	[Governing Group]
Resident
xwalk;

drop table xwalk;

exit script;
edwin
Specialist III
Specialist III

with the new DM:

edwin_1-1602900552556.png

 

 

Brett_Bleess
Support (Former)
Support (Former)

Following Design Blog should be of some help:

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

Cheers,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.