Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
asmithids
Partner - Creator II
Partner - Creator II

Loops in Data Model

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. 

1 Solution

Accepted Solutions
MarcoWedel

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

View solution in original post

7 Replies
MarcoWedel

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

asmithids
Partner - Creator II
Partner - Creator II
Author

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]);
 
 


MarcoWedel

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

asmithids
Partner - Creator II
Partner - Creator II
Author

Hi Marco,

That worked GREAT!!!   Thank you very much!!!

Happy Holidays!  

MarcoWedel

you're welcome.

Happy holidays for you also.

regards

Marco

MarcoWedel

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%20d...] (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%20d...] (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

asmithids
Partner - Creator II
Partner - Creator II
Author

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,