Circular Loop In Qlik sense

[Mouni09]

Hi All,

There is a script in my Qlik sense where I am trying to give relationship between Sales table and targets table based on User_ID, but it's forming circular loop how to reomve that. In front end I want to show revenueper user id as well as target per user id. Let me know if I'm doing anything wrong

[Currency]:

LOAD

    Currency,

    ExchangeRateToUSD

FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is Currency);

[Customers]:

LOAD

    Customer_ID,

    Customer_Name,

    City,

    Currency

FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is Customers);

[Products]:

LOAD

    Product_ID,

    Product_Name,

    "Price ($)" as Price_USD,

    Category

FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is Products);

[Sales Items]:

LOAD

    Sale_ID,

    "Line ID",

    "Product ID" as Product_ID,

    Qnt,

    "Revenue (Customer's Currency)" as Revenue_Customer_Currency

FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is [Sales Items]);

[Sales]:

LOAD

    Sale_ID,

    "Customer ID" as Customer_ID,

    "Sales Date" as Sales_Date,

"Owner ID (User ID)" as User_ID  

FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is Sales);

[Targets]:

LOAD

    "User ID" as User_ID,

    "Target (User's Currency)" as Target_User_Currency

FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is Targets);

[Users]:

LOAD

    User_ID,

    User_Name,

    Manager_ID,

    Currency,

    Created_date

FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is Users);

[Clement15]

Hello,

It's not perfect but this should work. You just have to be careful about the ExchangeRateToUSD field you use depending on the measurement

[Currency]:

LOAD

    Currency,

    ExchangeRateToUSD

FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is Currency);

Currency_MAP:
Mapping Load
Currency,
ExchangeRateToUSD
Resident Currency;

drop table Currency;

[Customers]:

LOAD

    Customer_ID,

    Customer_Name,

    City,

    ApplyMap('Currency_MAP',Currency) as ExchangeRateToUSD_Customers


FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is Customers);




[Products]:

LOAD

    Product_ID,

    Product_Name,

    "Price ($)" as Price_USD,

    Category

FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is Products);




[Sales Items]:

LOAD

    Sale_ID,

    "Line ID",

    "Product ID" as Product_ID,

    Qnt,

    "Revenue (Customer's Currency)" as Revenue_Customer_Currency

FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is [Sales Items]);










[Sales]:

LOAD

    Sale_ID,

    "Customer ID" as Customer_ID,

    "Sales Date" as Sales_Date,

"Owner ID (User ID)" as User_ID  




FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is Sales);







[Targets]:

LOAD

    "User ID" as User_ID,

    "Target (User's Currency)" as Target_User_Currency

FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is Targets);




[Users]:

LOAD

    User_ID,

    User_Name,

    Manager_ID,

    ApplyMap('Currency_MAP',Currency) as ExchangeRateToUSD_Users,


    Created_date

FROM [lib://DataFiles/Sales Data (1).xlsx]

(ooxml, embedded labels, table is Users);

[Mouni09]

I don't know why I am seeing exchange rate to usd different for 

ExchangeRateToUSD_Users and

ExchangeRateToUSD_Customers

why do we need them?

Below are my dataset and qvf files, if you have some time could you please check?
It's just a siample dataset which will not have more than 20 rows
 

[Clement15]

Hello,

In your formula you must choose the right ExchangeRateToUSD according to your needs and business rules