7 Replies Latest reply: Dec 22, 2014 1:58 PM by Alec Smith RSS

    Loops in Data Model

    Alec Smith

      Hello,

       

      I am getting loops in my data model (see attached) that I am not sure why they are occurring.  The data model seems clean, but I'm just not seeing the problem.  Thank you in advance for any assistance with this issue. 

        • Re: Loops in Data Model
          Marco Wedel

          Hi,

           

          why dous your Risk table have to be linked through 4 link tables to the "Test correspondent bank data" table?

          Can you combine the X_Risk tables into one table and use this as link between the others?

          Or maybe you could link with the field CountryCode itself? The additional information of the X_CountryCode fields could be concatenated and used as a seperate dimension table for the "Test correspondent bank data" table.

           

          To clarify please post sample data.

           

          hope this helps

           

          regards

           

          Marco

            • Re: Loops in Data Model
              Alec Smith

              Hi Marco,

               

              Thank you for responding. When I concatenate the risk tables into one, I get synthetic keys.  Here is the load script and I attached the updated model viewer and the source data.  I think the separate O_BIC, S_BIC, R_BIC, B_BIC, fields in the Test correspondent bank data file is the issue, but I'm not sure how to group or remove the loops.  I basically need to be able to filter the Test correspondent bank data by the dimensions in last Load Statement called "CountryRisk". 

               

              Transactions:
              LOAD transaction_key,
              AMOUNT,
              DATE_MONTH,
              ORIGINATOR_BANK_SWIFT,
              MID(O_BIC_CODE,5,2) as O_CountryCode,
              SENDER_BANK_SWIFT,
              MID(S_BIC_CODE,5,2) as S_CountryCode,
              RECEIVING_BANK_SWIFT,
              MID(R_BIC_CODE,5,2) as R_CountryCode,
              BENEFICIARY_BANK_SWIFT,
              MID(B_BIC_CODE,5,2) as B_CountryCode
              FROM
              [Test correspondent bank data EDITED.xlsx]
              (ooxml, embeddedlabels, tableis [Test correspondent bank data]);

               

               

              Risk:
              LOAD CountryCode AS O_CountryCode,
              CountryCode
              FROM
              [BANK BIC Codes and Country Risk.xlsx]
              (
              ooxml, embedded labels, table is [country risk]);

              Concatenate
              LOAD CountryCode AS S_CountryCode,
              CountryCode
              FROM
              [BANK BIC Codes and Country Risk.xlsx]
              (
              ooxml, embedded labels, table is [country risk]);

              Concatenate
              LOAD CountryCode AS R_CountryCode,
              CountryCode
              FROM
              [BANK BIC Codes and Country Risk.xlsx]
              (
              ooxml, embedded labels, table is [country risk]);

              Concatenate
              LOAD CountryCode AS B_CountryCode,
              CountryCode
              FROM
              [BANK BIC Codes and Country Risk.xlsx]
              (
              ooxml, embedded labels, table is
              [country risk]);

              CountryRisk:
              LOAD CountryCode,
                  Country,
                  WorldRegion,
                  CountryRiskDesignation,
                  Score
              FROM
              [BANK BIC Codes and Country Risk.xlsx]
              (ooxml, embedded labels, table is [country risk]);
               
               


                • Re: Loops in Data Model
                  Marco Wedel

                  Hi,

                   

                  one solution could be:

                   

                  QlikCommunity_Thread_146442_Pic1.JPG

                   

                  QlikCommunity_Thread_146442_Pic2.JPG

                   

                  QlikCommunity_Thread_146442_Pic3.JPG

                   

                  Transactions:
                  LOAD *,
                      AutoNumberHash128(O_CountryCode,S_CountryCode,R_CountryCode,B_CountryCode) as %CCID;
                  LOAD transaction_key,
                  AMOUNT,
                  DATE_MONTH,
                  ORIGINATOR_BANK_SWIFT,
                  MID(O_BIC_CODE,5,2) as O_CountryCode,
                  SENDER_BANK_SWIFT,
                  MID(S_BIC_CODE,5,2) as S_CountryCode,
                  RECEIVING_BANK_SWIFT,
                  MID(R_BIC_CODE,5,2) as R_CountryCode,
                  BENEFICIARY_BANK_SWIFT,
                  MID(B_BIC_CODE,5,2) as B_CountryCode
                  FROM [Test correspondent bank data EDITED.xlsx] (ooxml, embedded labels, table is [Test correspondent bank data]);
                  
                  Risk:
                  CrossTable (CountryRole, CountryCode)
                  LOAD Distinct
                      %CCID,
                      O_CountryCode as Originator,
                      S_CountryCode as Sender,
                      R_CountryCode as Receiver,
                      B_CountryCode as Beneficiary
                  Resident Transactions;
                  
                  
                  CountryRisk:
                  LOAD CountryCode,
                      Country,
                      WorldRegion,
                      CountryRiskDesignation,
                      Score
                  FROM [Test correspondent bank data EDITED.xlsx] (ooxml, embedded labels, table is Risk);
                  

                   

                  hope this helps

                   

                  regards

                   

                  Marco

              • Re: Loops in Data Model
                Marco Wedel

                Hi,

                 

                one addition:

                 

                it might be useful to load the CountryRisk table multiple times (for each role) with changed field names to get seperate master tables for each role together with one master table for common dimension analysis:

                 

                QlikCommunity_Thread_146442_Pic4.JPG

                 

                QlikCommunity_Thread_146442_Pic5.JPG

                 

                QlikCommunity_Thread_146442_Pic6.JPG

                 

                 

                Transactions:
                LOAD *,
                     AutoNumberHash128(O_CountryCode,S_CountryCode,R_CountryCode,B_CountryCode) as %CCID;
                LOAD *,
                     MID(O_BIC_CODE,5,2) as O_CountryCode,
                     MID(S_BIC_CODE,5,2) as S_CountryCode,
                     MID(R_BIC_CODE,5,2) as R_CountryCode,
                     MID(B_BIC_CODE,5,2) as B_CountryCode
                FROM [http://community.qlik.com/servlet/JiveServlet/download/683958-142642/Test%20correspondent%20bank%20data%20EDITED.xlsx] (ooxml, embedded labels, table is [Test correspondent bank data]);
                
                Risk:
                CrossTable (CountryRole, CountryCode)
                LOAD Distinct
                     %CCID,
                     O_CountryCode as Originator,
                     S_CountryCode as Sender,
                     R_CountryCode as Receiver,
                     B_CountryCode as Beneficiary
                Resident Transactions;
                
                CountryRisk:
                LOAD *
                FROM [http://community.qlik.com/servlet/JiveServlet/download/683958-142642/Test%20correspondent%20bank%20data%20EDITED.xlsx] (ooxml, embedded labels, table is [Risk]);
                
                FOR Each vRole in 'O','S','R','B';
                 $(vRole)_CountryRisk:
                 LOAD CountryCode as $(vRole)_CountryCode,  
                      Country as $(vRole)_Country,  
                      WorldRegion as $(vRole)_WorldRegion,  
                      CountryRiskDesignation as $(vRole)_CountryRiskDesignation,  
                      Score as $(vRole)_Score
                 Resident CountryRisk;
                NEXT;
                

                 

                 

                 

                Interesting thoughts regarding this topic can be found here:

                 

                Why You sometimes should Load a Master Table several times

                 

                Linking to two or more dates

                 

                Canonical Date

                 

                 

                hope this helps

                 

                regards

                 

                Marco

                  • Re: Loops in Data Model
                    Alec Smith

                    Hi Marco,

                     

                    Thank you for the additional scripting and reference material.  I think your original script is the best option as I need to create analytics by country and then by all of the other dimensions.  I have successfully tested the data model against the source data.  The data model is working perfectly! 

                     

                    Thank you again,

                     

                    Cheers,