I have an application with the following structure.
I have MATL which is within a group SQUARE, which is part of a group SQUAREGROUP.
Within each SQUAREGROUP, users define length bands for grouping MATL, - these are converted in a crosstable so we have within each SQUAREGROUP a RANGE IN BAND has limits that need to be associated with the length of the material. Currently this is done by linking to LGTHfloor for each foot of material.
Looking from the MATL side, MATL has a length which is converted to LGTHfloor.
MATL and LGTHfloor are part of a Shipments table.
The SQUARE relationship is captured through a link to a PRODUCT_DESCRIPTION table.
The SQUAREGROUP, RANGE IN BAND and LGTHfloor are part of a RANGECROSS table.
The objective is to be able to provide summaries by SQUAREGROUP, SQUARE, MATL and RANGE IN BAND, where the length of the material would fall within the RANGE IN BAND.
I've tried several structures to get around the "Loosely Coupled" message I get from QlikView, but without success. I get either the loosely coupled message and not the result I want, or no message but erroneous results.
Posting reply in two pieces: this is piece one of two
Here, I have attached a QlikView I used to test the end-to-end of just the grouping from SquareGroupBands down through the groupings and lengths included in those SquareGroupBands. This part of the functionality works fine. The problem has come when I try to link the items in Shipments into this structure (The LGTHfloor in Shipments should show up in the SquareGroupBands etc. grouping.
Here is the structure of the Excel file which is used as the basis for the cross-table:
I've attached a Word document with a screen shot of the current table structure.
The Excel file on which the cross table is used has a structure per below:
LGTHfloor 25to6Band 7to8Band 6P40
19 1 2 3
20 2 3 3
21 2 4 3
22 2 4 3
23 2 4 3
24 3 4 3
The user inputs the "1, 2, 3, 4" numbers for each value of LGTHfloor and each band. LGTHfloor is a qualifying field. The Bands variable is SquareGroupBand, and the value of each position is RANGEINBAND.
LGTHfloor is also floor(LGTH) for each of the elements in the Shipments table.
I haven't fully got my head around what you are trying to achieve, but may I suggest you try looking at the MAPPING LOAD and ApplyMap statements in the help file. This will allow you to pull fields from one table into another, and then lose the key that was holding them together. It is ideal for getting rid of 'ID / Value' type tables in your data model, but by layering a number of ApplyMap statements you can do more with them.
Good luck. Post back to let us know how you get on.
Did you over come this issue. I am also facing lots loosely coupled keys in my data structure and don't have any idea to overcome that issue. Help me out if you have solved this issue or else we at least discuss our view to solve this issue.