Qlik Compose SQL compilation error: invalid identifier after upgrade to 2023.11 or 2024.12

[sureshkumar]

After upgrading Qlik Compose from 2022.5 to 2023.11 or 2024.12, attempting to generate and run the Data Mart job fails with the following error:

SQL compilation error: invalid identifier "MASTER_CONSIGNMENTLEG_MASTER_CONSIGNMENT_GLOBI_consignment_INVOICE_GLOBI_relations_OID" 

Resolution

To resolve, change the "is_pk" property from false to true for the OID columns in the Aggregate Fact definition.

For new Data Mart jobs, the OID column defaults to true. This issue only affects older Aggregate Facts that include dimensions.

Detailed steps:

1. Create a deployment package of the project where the issue exists.
2. Extract the ZIP file and open the JSON file.
3. Locate the Aggregate Fact table definition in the JSON file that has the issue. For example: FACT_GLOBI_AGG_FINANCIAL_PLAN_NON_FIN_REP.
   
   
   
   
4. Locate the _OID columns within the Aggregate Fact definition.
   For example, FACT_GLOBI_AGG_FINANCIAL_PLAN_NON_FIN_REP contains four OID columns:
   • S_sec_department_id_GLOBI_department_GLOBI_department_OID
   • S_invoice_relation_id_GLOBI_relations_GLOBI_relations_OID
   • S_sec_branch_id_GLOBI_branchGLOBI_branch_OID
   • S_sec_branch_id_GLOBI_relation_address_GLOBI_branch_OID
5. For each _OID column, check the value of the "is_pk" property.
6. If "is_pk" is set to false for any _OID column, update it to true.
7. Save the changes, then re-ZIP the JSON file along with the readme.txt file.
8. Deploy the updated ZIP file into the existing project.
9. Regenerate the statements for the Data Mart.
10. Run the Datamart ETL job.

Environment

• Qlik Compose