Skip to main content

Circular references

ON THIS PAGE

Circular references

If there are circular references (loops) in a data structure, the tables are associated in such a way that there is more than one path of associations between two fields.

  1. Open the Data load editor in the Scripting Tutorial app.
  2. Click Create new to add a new script section.
  3. Name the section Region.
  4. Under DataFiles in the right menu, click Select data.

  5. Upload and then select Region.txt. A data preview window opens.
  6. Select all fields and make sure that Embedded field names under Field names is selected to include the names of the table fields when you load the data.
  7. Click Insert script.
  8. Click Load data.
  9. This time it appears that something has gone wrong with your data load. A circular reference has been created. The Data load progress window will show an error message, stating that a circular reference was found during the load. However, the load is completed and the app is saved.

  10. Open the Data model viewer.
  11. You can drag the tables around to reorganize them in a way so that the connections between the tables are easy to see.

    Data model viewer showing circular reference
    Data model viewer showing circular reference.

    The red dotted lines indicate that a circular reference has been created. This is something that you should avoid, because it may cause ambiguities in the data interpretation.

Resolving circular references

To be able to understand what caused the circular references, let us take a closer look at your tables in the Data model viewer.

If you look at Table1 and the Table2 in the screenshot above, you can see that they have the Date field in common. You can also see that Table1 and Region have the SalesKey field in common. Finally, notice that the Table2 and Region have the field Week in common. This means that a loop, a circular reference, has been created. Since this can cause problems in the data analysis later on, let us remove it.

The easiest way to resolve this is to rename or remove one of the fields. In our case, we have loaded some data that we do not need for our app, and we can remove it.

  1. Open the Data load editor.
  2. Click on the section Region and delete the following two rows in the LOAD statement:
  3. "Week",

    SalesKey

  4. Make sure to also remove the comma after "Region Code".
  5. Your script should now appear as follows:

    LOAD
        Region,
        "Region Code"
    FROM [lib://DataFiles/Region.txt]
    (txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

  6. Click Load data.
  7. Open the Data model viewer.
  8. The undesired references to Region has been removed.

    Data model viewer showing that circular reference has been removed
    Data model viewer showing that circular reference has been removed.