topic Re: Circular Loop In Qlik sense in App Development

Circular Loop In Qlik sense

Mouni09 — Tue, 18 Feb 2025 14:18:32 GMT

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,

Re: Circular Loop In Qlik sense

Clement15 — Tue, 18 Feb 2025 15:08:10 GMT

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

Re: Circular Loop In Qlik sense

Mouni09 — Tue, 18 Feb 2025 15:33:18 GMT

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

Re: Circular Loop In Qlik sense

Clement15 — Wed, 19 Feb 2025 10:49:11 GMT

Hello,

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