Q-Expert · Real cases · Q-Expert DistributorsLeer en español

Episode4

Two currencies

Step-by-step manual

01The business question

Q-Expert Distributors sells in pesos and in dollars. Every sale must be valued in pesos with the exchange rate that was valid on its own date, and placed in an amount band. On the way, capture errors and discontinued products have to leave the analysis.

02Before you start

Unzip the episode package: ExchangeRates.csv, AmountBands.csv, the files of the previous episodes and start.qep (episode 3's end). Add the two new files to the Library.
Import start.qep with Import QEP. First, delete episode 3's debugging CHECKPOINT after Customer: it stops the reload there.
Want to check the rates before modeling? Open ExchangeRates.csv in Q-Explorator, the Q-Fábrica team's desktop tool: every row is a currency with a validity range.

03Step by step

1
Clean the sales

Add a TXF tmp_Sales after tmp_SalesRaw. On Expressions:
- Date(Date#(SaleDate, 'YYYY-MM-DD')) as SaleDate
- ApplyMap('map_CustomerSurvivor', CustomerID, -1) as CustomerID
- ApplyMap('map_ChannelName', Channel, 'Other') as ChannelName
- Quantity * UnitPrice * (1 - DiscountPct / 100) as NetAmount
Turn on WHERE with Quantity > 0. Then DROP tmp_SalesRaw.

Note
A sale whose customer is not in the master gets -1, the Unknown customer member of episode 3: it still counts.

Figure 4.1 — Clean the sales
2
Keep only active products

Add a JOIN node, operation INNER JOIN, between Product (only its ProductID) and tmp_Sales. Sales of discontinued products leave the analysis.

Note
That is a business rule, not an error: the business decided discontinued products are out of this analysis.

Figure 4.2 — Keep only active products
3
Find each sale's valid rate

Load ExchangeRates.csv as tmp_ExchangeRate, with Date#(…, 'YYYY-MM-DD') on ValidFromDate and ValidToDate. Then add an INTERVALMATCH tmp_RateBridge:
- Match field SaleDate
- Start ValidFromDate, end ValidToDate
- Additional key Currency
Figure 4.3 — Find each sale's valid rate
4
Bring the rate into the sales

Three nodes and a cleanup:
- A TXF tmp_SaleRate (NoConcatenate) reads the bridge.
- A LEFT JOIN adds Rate to it from tmp_ExchangeRate.
- A second LEFT JOIN brings SaleDate, Currency, Rate into tmp_Sales, with the WHERE Currency <> '$(vBaseCurrency)'.
- A DROP of the three work tables.
Figure 4.4 — Bring the rate into the sales
5
Value every sale in pesos

A TXF Sales (NoConcatenate) reads tmp_Sales and adds Round(NetAmount * Alt(Rate, 1)) as NetAmountCOP. Then DROP tmp_Sales.

Note
Sales in pesos have no rate; Alt(Rate, 1) gives them 1, so their amount stays as it is.

Figure 4.5 — Value every sale in pesos
6
Place each sale in an amount band

The same pattern again:
- tmp_AmountBand from AmountBands.csv.
- An INTERVALMATCH tmp_BandBridge on NetAmountCOP between FromAmount and ToAmount.
- A bridge tmp_SaleBand with the band joined in.
- A LEFT JOIN of AmountBand into Sales.
- A DROP of the work tables and of the field SourceFile.
Figure 4.6 — Place each sale in an amount band
7
Store the sales, then stop and look

Two checkpoints after Sales:
- STORE to $(vTxfPath)Sales.qvd: the Transform layer's sales table.
- A debugging one that stores $(vTxfPath)Sales_Check.qvd with Exit Script ticked, so the reload stops while you check the sales.
Figure 4.7 — Store the sales, then stop and look
8
Generate the script

Generate Syntax. The mapping tables load first; the last tab ends with the work tables dropped, DROP FIELD SourceFile, STORE [Sales] INTO …Sales.qvd and the debugging STORE + EXIT SCRIPT.

Figure 4.8 — Generate the script

04Check your result

Run the script in Qlik. It stops at the debugging checkpoint, with Transform/Sales.qvd and Transform/Sales_Check.qvd written.
Sales has 298 rows: 330 read, 8 with a quantity of zero or less left out by the WHERE, 24 of discontinued products left out by the INNER JOIN. 37 are in USD and carry a Rate; the 261 in COP do not. 11 go to the unknown customer (-1).
Q-Expert splits the script into _Main, the only part you paste into Qlik, and one .qvs file per tab that _Main reads with MUST_INCLUDE. All of them should match the ones below, except the date in the header and a // Q-Expert · Ref … line with your own reference.

_Main

Paste into Qlik

This is the only script you paste into Qlik Sense: open the app's Data load editor, replace its content with this and reload. Each $(MUST_INCLUDE=…) line reads one of the files below in its place, so the rest of the model never goes into the editor.

Show script · 47 lines

/*----------------------------------------------------------------------------------------
	Description [MAIN]:
		
	-------------------------------------------------------------------------------------
	Author:	The Q-Fábrica team - 
	Date:	[2026-11-02]
	-------------------------------------------------------------------------------------
	Changes
	-------------------------------------------------------------------------------------
	Author		Date		Detail
	-------------------------------------------------------------------------------------

	-------------------------------------------------------------------------------------
	Copyright © Q-Expert Distributors.
	Q-Expert Qlik Developer Framework. www.qfabrica.com
----------------------------------------------------------------------------------------*/

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$ #,##0.00;-$ #,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD hh:mm:ss';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;

LET vExecutionDate = Timestamp(Now(), 'YYYY-MM-DD hh:mm:ss');

SET vTxfPath  = 'lib://QXDistributors/Transform/';
SET vPathRaw = 'lib://QXDistributors/';
SET vEnvironment = 'PROD';   // PROD, QA or DEV
SET vBaseCurrency = 'COP';   // Currency every amount is converted to
SET vPathModel = 'lib://QXDistributors/Model/';   // The model QVDs the apps load

$(MUST_INCLUDE=$(vTxfPath)_QVS/Flow_1.qvs);
$(MUST_INCLUDE=$(vTxfPath)_QVS/Flow_2.qvs);
$(MUST_INCLUDE=$(vTxfPath)_QVS/Flow_3.qvs);
$(MUST_INCLUDE=$(vTxfPath)_QVS/Flow_4.qvs);
$(MUST_INCLUDE=$(vTxfPath)_QVS/Sales.qvs);

Flow_1.qvs

File · read by MUST_INCLUDE

Not pasted: saved as a file. On the Flow_1 tab of Generate Syntax, Export downloads Flow_1.qvs (Deploy writes every tab into a folder at once). Put it in lib://QXDistributors/Transform/_QVS/Flow_1.qvs, the Transform/_QVS folder inside your QXDistributors folder. If the file is not there, the reload stops with an error: that is what MUST_INCLUDE is for.

Show script · 35 lines

/*----------------------------------------------------------------------------------------
	Description [Flow_1]:
		
	-------------------------------------------------------------------------------------
	Author:	The Q-Fábrica team - 
	Date:	[2026-11-02]
	-------------------------------------------------------------------------------------
	Changes
	-------------------------------------------------------------------------------------
	Author		Date		Detail
	-------------------------------------------------------------------------------------

	-------------------------------------------------------------------------------------
	Copyright © Q-Expert Distributors.
	Q-Expert Qlik Developer Framework. www.qfabrica.com
----------------------------------------------------------------------------------------*/

// -------------------------------------------------------------------------------------------------
// Table defined directly in the script (2 columns).
[map_DocType_Src]:
    LOAD * INLINE [
    "DocType", "DocTypeName"
    "NIT", "Company"
    "CC", "Person"
    ];

// -------------------------------------------------------------------------------------------------
// Mapping table (DocType → DocTypeName) from map_DocType_Src, for ApplyMap.
[map_DocType]:
    MAPPING LOAD
        DocType,
        DocTypeName
    RESIDENT [map_DocType_Src];

DROP TABLE [map_DocType_Src];

Flow_2.qvs

File · read by MUST_INCLUDE

Not pasted: saved as a file. On the Flow_2 tab of Generate Syntax, Export downloads Flow_2.qvs (Deploy writes every tab into a folder at once). Put it in lib://QXDistributors/Transform/_QVS/Flow_2.qvs, the Transform/_QVS folder inside your QXDistributors folder. If the file is not there, the reload stops with an error: that is what MUST_INCLUDE is for.

Show script · 36 lines

/*----------------------------------------------------------------------------------------
	Description [Flow_2]:
		
	-------------------------------------------------------------------------------------
	Author:	The Q-Fábrica team - 
	Date:	[2026-11-02]
	-------------------------------------------------------------------------------------
	Changes
	-------------------------------------------------------------------------------------
	Author		Date		Detail
	-------------------------------------------------------------------------------------

	-------------------------------------------------------------------------------------
	Copyright © Q-Expert Distributors.
	Q-Expert Qlik Developer Framework. www.qfabrica.com
----------------------------------------------------------------------------------------*/

// -------------------------------------------------------------------------------------------------
// Table defined directly in the script (2 columns).
[map_CustomerStatus_Src]:
    LOAD * INLINE [
    "Status", "CustomerStatus"
    "A", "Active"
    "I", "Inactive"
    "B", "Blocked"
    ];

// -------------------------------------------------------------------------------------------------
// Mapping table (Status → CustomerStatus) from map_CustomerStatus_Src, for ApplyMap.
[map_CustomerStatus]:
    MAPPING LOAD
        Status,
        CustomerStatus
    RESIDENT [map_CustomerStatus_Src];

DROP TABLE [map_CustomerStatus_Src];

Flow_3.qvs

File · read by MUST_INCLUDE

Not pasted: saved as a file. On the Flow_3 tab of Generate Syntax, Export downloads Flow_3.qvs (Deploy writes every tab into a folder at once). Put it in lib://QXDistributors/Transform/_QVS/Flow_3.qvs, the Transform/_QVS folder inside your QXDistributors folder. If the file is not there, the reload stops with an error: that is what MUST_INCLUDE is for.

Show script · 36 lines

/*----------------------------------------------------------------------------------------
	Description [Flow_3]:
		
	-------------------------------------------------------------------------------------
	Author:	The Q-Fábrica team - 
	Date:	[2026-11-02]
	-------------------------------------------------------------------------------------
	Changes
	-------------------------------------------------------------------------------------
	Author		Date		Detail
	-------------------------------------------------------------------------------------

	-------------------------------------------------------------------------------------
	Copyright © Q-Expert Distributors.
	Q-Expert Qlik Developer Framework. www.qfabrica.com
----------------------------------------------------------------------------------------*/

// -------------------------------------------------------------------------------------------------
// Table defined directly in the script (2 columns).
[map_ChannelName_Src]:
    LOAD * INLINE [
    "Channel", "ChannelName"
    "P", "Phone"
    "T", "In store"
    "W", "Web"
    ];

// -------------------------------------------------------------------------------------------------
// Mapping table (Channel → ChannelName) from map_ChannelName_Src, for ApplyMap.
[map_ChannelName]:
    MAPPING LOAD
        Channel,
        ChannelName
    RESIDENT [map_ChannelName_Src];

DROP TABLE [map_ChannelName_Src];

Flow_4.qvs

File · read by MUST_INCLUDE

Not pasted: saved as a file. On the Flow_4 tab of Generate Syntax, Export downloads Flow_4.qvs (Deploy writes every tab into a folder at once). Put it in lib://QXDistributors/Transform/_QVS/Flow_4.qvs, the Transform/_QVS folder inside your QXDistributors folder. If the file is not there, the reload stops with an error: that is what MUST_INCLUDE is for.

Show script · 24 lines

/*----------------------------------------------------------------------------------------
	Description [Flow_4]:
		
	-------------------------------------------------------------------------------------
	Author:	The Q-Fábrica team - 
	Date:	[2026-11-02]
	-------------------------------------------------------------------------------------
	Changes
	-------------------------------------------------------------------------------------
	Author		Date		Detail
	-------------------------------------------------------------------------------------

	-------------------------------------------------------------------------------------
	Copyright © Q-Expert Distributors.
	Q-Expert Qlik Developer Framework. www.qfabrica.com
----------------------------------------------------------------------------------------*/

// -------------------------------------------------------------------------------------------------
// Mapping table (BrandID → BrandName) from $(vPathRaw)Brands.txt, for ApplyMap.
[map_BrandName]:
    MAPPING LOAD
        BrandID,
        BrandName
    FROM [$(vPathRaw)Brands.txt] (txt, utf8, delimiter is '|', embedded labels, msq);

Sales.qvs

File · read by MUST_INCLUDE

Not pasted: saved as a file. On the Sales tab of Generate Syntax, Export downloads Sales.qvs (Deploy writes every tab into a folder at once). Put it in lib://QXDistributors/Transform/_QVS/Sales.qvs, the Transform/_QVS folder inside your QXDistributors folder. If the file is not there, the reload stops with an error: that is what MUST_INCLUDE is for.

Show script · 421 lines

/*----------------------------------------------------------------------------------------
	Description [Sales]:
		
	-------------------------------------------------------------------------------------
	Author:	The Q-Fábrica team - 
	Date:	[2026-11-02]
	-------------------------------------------------------------------------------------
	Changes
	-------------------------------------------------------------------------------------
	Author		Date		Detail
	-------------------------------------------------------------------------------------

	-------------------------------------------------------------------------------------
	Copyright © Q-Expert Distributors.
	Q-Expert Qlik Developer Framework. www.qfabrica.com
----------------------------------------------------------------------------------------*/

// -------------------------------------------------------------------------------------------------
// Empty table with 1 column (WHERE 1=0), schema only.
[tmp_SalesRaw]:
    LOAD * INLINE [
    "Dummy"
    ] WHERE 1=0;

// -------------------------------------------------------------------------------------------------
// Loop FOR EACH … IN FileList() over $(vFile).
FOR EACH vFile IN FileList('$(vPathRaw)Sales_*.csv')

    // -------------------------------------------------------------------------------------------------
    // Raw load from $(vFile), concatenated into [tmp_SalesRaw].
    CONCATENATE ([tmp_SalesRaw])
    LOAD
        SaleID,
        SaleDate,
        CustomerID,
        ProductID,
        SalespersonID,
        BranchID,
        Quantity,
        UnitPrice,
        DiscountPct,
        Currency,
        Channel,
        FileName()                                                                       AS SourceFile
    FROM [$(vFile)] (txt, utf8, delimiter is ',', embedded labels, msq);

NEXT vFile;

DROP FIELD Dummy FROM [tmp_SalesRaw];

LET vSalesRawRows = NoOfRows('tmp_SalesRaw');

TRACE Sales rows read from the monthly files: $(vSalesRawRows);

// -------------------------------------------------------------------------------------------------
// Raw load of tmp_Parameter (no source set yet).
[tmp_Parameter]:
    LOAD
        Parameter,
        Value
    FROM [$(vPathRaw)Parameters.txt] (txt, utf8, delimiter is '	', embedded labels, msq);

LET vCreditThreshold = Lookup('Value', 'Parameter', 'CreditThreshold', 'tmp_Parameter');

DROP TABLE [tmp_Parameter];

// -------------------------------------------------------------------------------------------------
// Mapping table (Num(Date#(HolidayDate, 'YYYY-MM-DD')) AS HolidayDate → HolidayName) from $(vPathRaw)Holidays.csv, for ApplyMap.
[map_Holiday]:
    MAPPING LOAD DISTINCT
        Num(Date#(HolidayDate, 'YYYY-MM-DD')) AS HolidayDate,
        HolidayName
    FROM [$(vPathRaw)Holidays.csv] (txt, utf8, delimiter is ',', embedded labels, msq);

QUALIFY *;

UNQUALIFY CustomerID, ProductID, SalespersonID, BranchID, CategoryID, BrandID, DocNumber;

// -------------------------------------------------------------------------------------------------
// Raw load of tmp_CustomerRaw (no source set yet).
[tmp_CustomerRaw]:
    LOAD
        CustomerID,
        CustomerName,
        DocType,
        DocNumber,
        City,
        Segment,
        SignupDate,
        Status,
        CreditLimit
    FROM [$(vPathRaw)Customers.csv] (txt, utf8, delimiter is ',', embedded labels, msq);

// -------------------------------------------------------------------------------------------------
// Raw load of tmp_ProductRaw (no source set yet).
[tmp_ProductRaw]:
    LOAD
        ProductID,
        SKU,
        ProductName,
        CategoryID,
        BrandID,
        ListPrice,
        UnitCost,
        UnitOfMeasure,
        Status
    FROM [$(vPathRaw)Products.csv] (txt, utf8, delimiter is ',', embedded labels, msq);

// -------------------------------------------------------------------------------------------------
// Raw load of tmp_SalespersonRaw (no source set yet).
[tmp_SalespersonRaw]:
    LOAD
        SalespersonID,
        SalespersonName,
        BranchID,
        HireDate,
        MonthlyTarget
    FROM [$(vPathRaw)Salespeople.csv] (txt, utf8, delimiter is ',', embedded labels, msq);

// -------------------------------------------------------------------------------------------------
// Raw load of tmp_BranchRaw (no source set yet).
[tmp_BranchRaw]:
    LOAD
        BranchID,
        BranchName,
        Region,
        City,
        Format
    FROM [$(vPathRaw)Branches.csv] (txt, utf8, delimiter is ',', embedded labels, msq);

// -------------------------------------------------------------------------------------------------
// Raw load of tmp_CategoryRaw (no source set yet).
[tmp_CategoryRaw]:
    LOAD
        CategoryID,
        CategoryName,
        Family
    FROM [$(vPathRaw)Categories.csv] (txt, utf8, delimiter is ',', embedded labels, msq);

UNQUALIFY *;

// -------------------------------------------------------------------------------------------------
// Raw load of tmp_BrandRaw (no source set yet).
[tmp_BrandRaw]:
    LOAD
        BrandID,
        BrandName,
        CountryOfOrigin
    FROM [$(vPathRaw)Brands.txt] (txt, utf8, delimiter is '|', embedded labels, msq);

// -------------------------------------------------------------------------------------------------
// Transforms tmp_CustomerRaw.
[tmp_CustomerClean]:
    LOAD
        CustomerID,
        DocNumber,
        [tmp_CustomerRaw.Segment]                                                        AS Segment,
        [tmp_CustomerRaw.CreditLimit]                                                    AS CreditLimit,
        Capitalize(Trim(tmp_CustomerRaw.CustomerName))                                   AS CustomerName,
        ApplyMap('map_DocType', tmp_CustomerRaw.DocType, 'Other')                        AS DocTypeName,
        If(Len(Trim(tmp_CustomerRaw.City)) = 0, 'No city', Trim(tmp_CustomerRaw.City))   AS CustomerCity,
        Date(Date#(tmp_CustomerRaw.SignupDate, 'DD/MM/YYYY'))                            AS SignupDate,
        ApplyMap('map_CustomerStatus', tmp_CustomerRaw.Status, 'Unknown')                AS CustomerStatus,
        If(tmp_CustomerRaw.CreditLimit >= $(vCreditThreshold), 'High', 'Standard')       AS CreditProfile,
        If(DocNumber = Previous(DocNumber), Peek('SurvivorID'), CustomerID)              AS SurvivorID
    RESIDENT [tmp_CustomerRaw]
    ORDER BY DocNumber, CustomerID;

// -------------------------------------------------------------------------------------------------
// Mapping table (CustomerID → SurvivorID) from tmp_CustomerClean, for ApplyMap.
[map_CustomerSurvivor]:
    MAPPING LOAD
        CustomerID,
        SurvivorID
    RESIDENT [tmp_CustomerClean];

// -------------------------------------------------------------------------------------------------
// Transforms tmp_CustomerClean, combined via CONCATENATE.
[Customer]:
    NOCONCATENATE
    LOAD
        CustomerID,
        DocNumber,
        Segment,
        CreditLimit,
        CustomerName,
        DocTypeName,
        CustomerCity,
        SignupDate,
        CustomerStatus,
        CreditProfile
    RESIDENT [tmp_CustomerClean]
    WHERE CustomerID = SurvivorID;

    CONCATENATE ([Customer])
    // -------------------------------------------------------------------------------------------------
    // Table defined directly in the script (3 columns).
    LOAD * INLINE [
    "CustomerID", "CustomerName", "CustomerStatus"
    "-1", "Unknown customer", "Unknown"
    ];

DROP TABLE [tmp_CustomerClean];

// -------------------------------------------------------------------------------------------------
// Transforms tmp_ProductRaw, combined via LEFT JOIN.
[Product]:
    LOAD
        ProductID,
        [tmp_ProductRaw.SKU]                                                             AS SKU,
        CategoryID,
        BrandID,
        [tmp_ProductRaw.ListPrice]                                                       AS ListPrice,
        [tmp_ProductRaw.UnitCost]                                                        AS UnitCost,
        [tmp_ProductRaw.UnitOfMeasure]                                                   AS UnitOfMeasure,
        Capitalize(Trim(tmp_ProductRaw.ProductName))                                     AS ProductName,
        ApplyMap('map_BrandName', BrandID, 'No brand')                                   AS BrandName,
        Round(
            (tmp_ProductRaw.ListPrice - tmp_ProductRaw.UnitCost) / tmp_ProductRaw.ListPrice,
            0.0001
        )                                                                                AS MarginPct
    RESIDENT [tmp_ProductRaw]
    WHERE tmp_ProductRaw.Status = 'ACTIVE';

    LEFT JOIN ([Product])
    // -------------------------------------------------------------------------------------------------
    // Transforms tmp_CategoryRaw.
    LOAD DISTINCT
        CategoryID,
        [tmp_CategoryRaw.CategoryName]                                                   AS CategoryName,
        [tmp_CategoryRaw.Family]                                                         AS Family
    RESIDENT [tmp_CategoryRaw];

    LEFT JOIN ([Product])
    // -------------------------------------------------------------------------------------------------
    // Transforms tmp_BrandRaw.
    LOAD
        BrandID,
        CountryOfOrigin
    RESIDENT [tmp_BrandRaw];

// -------------------------------------------------------------------------------------------------
// Transforms tmp_SalespersonRaw, combined via LEFT JOIN.
[Salesperson]:
    LEFT KEEP ([tmp_SalesRaw])
    LOAD
        SalespersonID,
        BranchID                                                                         AS HomeBranchID,
        [tmp_SalespersonRaw.MonthlyTarget]                                               AS MonthlyTarget,
        Capitalize(Trim(tmp_SalespersonRaw.SalespersonName))                             AS SalespersonName,
        Date(Date#(tmp_SalespersonRaw.HireDate, 'YYYY-MM-DD'))                           AS HireDate
    RESIDENT [tmp_SalespersonRaw];

// -------------------------------------------------------------------------------------------------
// Transforms tmp_BranchRaw.
[Branch]:
    LOAD
        BranchID,
        [tmp_BranchRaw.BranchName]                                                       AS BranchName,
        [tmp_BranchRaw.Region]                                                           AS Region,
        [tmp_BranchRaw.City]                                                             AS BranchCity,
        [tmp_BranchRaw.Format]                                                           AS BranchFormat
    RESIDENT [tmp_BranchRaw];

DROP TABLE [tmp_BranchRaw];
DROP TABLE [tmp_BrandRaw];
DROP TABLE [tmp_CategoryRaw];
DROP TABLE [tmp_CustomerRaw];
DROP TABLE [tmp_ProductRaw];
DROP TABLE [tmp_SalespersonRaw];

// -------------------------------------------------------------------------------------------------
// Raw load of tmp_ExchangeRate (no source set yet).
[tmp_ExchangeRate]:
    LOAD
        Currency,
        Rate,
        Date#(ValidFromDate, 'YYYY-MM-DD')                                               AS ValidFromDate,
        Date#(ValidToDate, 'YYYY-MM-DD')                                                 AS ValidToDate
    FROM [$(vPathRaw)ExchangeRates.csv] (txt, utf8, delimiter is ',', embedded labels, msq);

// -------------------------------------------------------------------------------------------------
// Associates SaleDate with the range [ValidFromDate, ValidToDate] from tmp_ExchangeRate.
[tmp_RateBridge]:
    INTERVALMATCH (SaleDate, Currency)
    LOAD
      ValidFromDate,
      ValidToDate,
      Currency
    RESIDENT [tmp_ExchangeRate];

// -------------------------------------------------------------------------------------------------
// Transforms tmp_RateBridge, combined via LEFT JOIN.
[tmp_SaleRate]:
    NOCONCATENATE
    LOAD
        SaleDate,
        ValidFromDate,
        ValidToDate,
        Currency
    RESIDENT [tmp_RateBridge];

    LEFT JOIN ([tmp_SaleRate])
    // -------------------------------------------------------------------------------------------------
    // Transforms tmp_ExchangeRate.
    LOAD
        Currency,
        Rate,
        ValidFromDate,
        ValidToDate
    RESIDENT [tmp_ExchangeRate];

// -------------------------------------------------------------------------------------------------
// Transforms tmp_SalesRaw, combined via INNER JOIN.
[tmp_Sales]:
    LOAD
        SaleID,
        ProductID,
        SalespersonID,
        BranchID,
        Quantity,
        UnitPrice,
        DiscountPct,
        Currency,
        SourceFile,
        Date(Date#(SaleDate, 'YYYY-MM-DD'))                                              AS SaleDate,
        ApplyMap('map_CustomerSurvivor', CustomerID, -1)                                 AS CustomerID,
        ApplyMap('map_ChannelName', Channel, 'Other')                                    AS ChannelName,
        Quantity * UnitPrice * (1 - DiscountPct / 100)                                   AS NetAmount
    RESIDENT [tmp_SalesRaw]
    WHERE Quantity > 0;

    INNER JOIN ([tmp_Sales])
    // -------------------------------------------------------------------------------------------------
    // Transforms Product.
    LOAD
        ProductID
    RESIDENT [Product];

    LEFT JOIN ([tmp_Sales])
    // -------------------------------------------------------------------------------------------------
    // Transforms tmp_SaleRate.
    LOAD
        SaleDate,
        Currency,
        Rate
    RESIDENT [tmp_SaleRate]
    WHERE Currency <> '$(vBaseCurrency)';

DROP TABLE [tmp_SalesRaw];

DROP TABLE [tmp_ExchangeRate];
DROP TABLE [tmp_RateBridge];
DROP TABLE [tmp_SaleRate];

// -------------------------------------------------------------------------------------------------
// Raw load of tmp_AmountBand (no source set yet).
[tmp_AmountBand]:
    LOAD
        FromAmount,
        ToAmount,
        Band                                                                             AS AmountBand
    FROM [$(vPathRaw)AmountBands.csv] (txt, utf8, delimiter is ',', embedded labels, msq);

// -------------------------------------------------------------------------------------------------
// Associates NetAmountCOP with the range [FromAmount, ToAmount] from tmp_AmountBand.
[tmp_BandBridge]:
    INTERVALMATCH (NetAmountCOP)
    LOAD
      FromAmount,
      ToAmount
    RESIDENT [tmp_AmountBand];

// -------------------------------------------------------------------------------------------------
// Transforms tmp_BandBridge, combined via LEFT JOIN.
[tmp_SaleBand]:
    NOCONCATENATE
    LOAD
        NetAmountCOP,
        FromAmount,
        ToAmount
    RESIDENT [tmp_BandBridge];

    LEFT JOIN ([tmp_SaleBand])
    // -------------------------------------------------------------------------------------------------
    // Transforms tmp_AmountBand.
    LOAD
        FromAmount,
        ToAmount,
        AmountBand
    RESIDENT [tmp_AmountBand];

// -------------------------------------------------------------------------------------------------
// Transforms tmp_Sales, combined via LEFT JOIN.
[Sales]:
    NOCONCATENATE
    LOAD
        *,
        Round(NetAmount * Alt(Rate, 1))                                                  AS NetAmountCOP
    RESIDENT [tmp_Sales];

    LEFT JOIN ([Sales])
    // -------------------------------------------------------------------------------------------------
    // Transforms tmp_SaleBand.
    LOAD
        NetAmountCOP,
        AmountBand
    RESIDENT [tmp_SaleBand];

DROP TABLE [tmp_Sales];

DROP TABLE [tmp_AmountBand];
DROP TABLE [tmp_BandBridge];
DROP TABLE [tmp_SaleBand];

DROP FIELD SourceFile FROM [Sales];

STORE [Sales] INTO [$(vTxfPath)Sales.qvd] (qvd);

STORE [Sales] INTO [$(vTxfPath)Sales_Check.qvd] (qvd);
EXIT SCRIPT;

05Why this way

IntervalMatch with a key. The rate depends on the date and on the currency. Adding Currency as a key makes each sale match only its own currency's ranges.
A bridge, then a join. The bridge table says which range each sale falls in; the rate itself comes in with a join. Keeping the steps apart keeps each one checkable.
The base currency in a variable. $(vBaseCurrency) decides which sales need a rate. Another company in another country changes one project variable.
Transform stores the clean table. Sales.qvd in Transform is what the model reads in episode 6; the second store is only for debugging and goes away.

06What comes next

Episode 5: budget against actual, net of returns.

Watch the episode video Next episode →

Before you model

Q-Explorator is the Q-Fábrica team's desktop tool to explore QVD, CSV, Excel and Parquet files and to convert CSV, TXT and Excel files into QVD. Open the files of each episode in it to see their content and quality first.

About Q-Explorator

01The business question

02Before you start

03Step by step

Clean the sales

Keep only active products

Find each sale's valid rate

Bring the rate into the sales

Value every sale in pesos

Place each sale in an amount band

Store the sales, then stop and look

Generate the script

04Check your result

_Main

Flow_1.qvs

Flow_2.qvs

Flow_3.qvs

Flow_4.qvs

Sales.qvs

05Why this way

06What comes next

Before you model