Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
asmithbi
Contributor 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. 

Tags (1)
1 Solution

Accepted Solutions

Re: Loops in Data Model

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

7 Replies

Re: Loops in Data Model

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

asmithbi
Contributor II

Re: Loops in Data Model

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

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

asmithbi
Contributor II

Re: Loops in Data Model

Hi Marco,

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

Happy Holidays!  

Re: Loops in Data Model

you're welcome.

Happy holidays for you also.

regards

Marco

Re: Loops in Data Model

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

asmithbi
Contributor II

Re: Loops in Data Model

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, 

Community Browser