topic Re: Loops in Data Model in QlikView

Loops in Data Model

asmithids — Sat, 20 Dec 2014 23:46:07 GMT

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

MarcoWedel — Sun, 21 Dec 2014 00:30:21 GMT

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

asmithids — Sun, 21 Dec 2014 01:13:04 GMT

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

MarcoWedel — Sun, 21 Dec 2014 12:05:43 GMT

Hi,

one solution could be:

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

asmithids — Sun, 21 Dec 2014 16:50:28 GMT

Hi Marco,

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

Happy Holidays!

Re: Loops in Data Model

MarcoWedel — Sun, 21 Dec 2014 22:50:39 GMT

you're welcome.

Happy holidays for you also.

regards

Marco

Re: Loops in Data Model

MarcoWedel — Mon, 22 Dec 2014 01:04:15 GMT

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:

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

asmithids — Mon, 22 Dec 2014 18:58:48 GMT

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,