Combining fields from other QVD + generate master/shared calendar for filtering

[Daryn]

Hello, I have a QVD containing order_number, invoice_number, as well as amount_invoice, amount_order, order_date and invoice_date as well as multiple other fields. The issue is amount_order is incorrect for many orders due to how an order can be priced (long story), so I want to use the order header value from VBAK (I have a VBAK QVD containing all fields), and would need VBELN, WAERK, and then KURSK from VBKD (as currency can be USD, EUR or GBP).  I had the below for calculating currency. I want to create a shared/master calendar I can use across all sheets whether they are reporting on order or invoice. My issue there was that invoice or order date would take preference pending load order, where as I want them both equal as to there respective order or invoice.

 TEMP:
 LOAD
 NUM(NUM#(VBELN, '#0')) as order_no,
 BSTDK,
 WAERK,
 Round(Num#(NETWR, '#.#############'), 0.01) as NETWR
 FROM [lib://QVD-Generator/VBAK.qvd] (qvd)
 WHERE fabs(Num#(NETWR, '#.#############')) >= 0.005;

 VBKD_Rate:
 LOAD
 NUM(NUM#(VBELN, '#0')) as order_no,
 Max(
 If(
 IsNum(Num#(Replace(KURSK, ',', '.'), '0.0000')),
 Num#(Replace(KURSK, ',', '.'), '0.0000')
 )
 ) as KURSK
 FROM [lib://QVD-Generator)/VBKD.qvd] (qvd)
 GROUP BY NUM(NUM#(VBELN, '#0'));

 LEFT JOIN (TEMP)
 LOAD order_no, KURSK
 RESIDENT VBKD_Rate;

 DROP TABLE VBKD_Rate;

 LOAD
 order_no,
 Sum(
 If(WAERK='GBP', NETWR,
 If(WAERK='EUR', NETWR * KURSK,
 If(WAERK='USD' AND KURSK<>0, NETWR / fabs(KURSK), Null())))
 ) as amountLC_order2
RESIDENT TEMP
GROUP BY order_no;

My main QVD is just called Sales_data and as mentioned has numerous other fields such as sales_office, order_quantity etc, but I can include those fields as required, my main issue is getting it all to work as one from that Sales_data with the VBAK and VBRK data and the calender and order_date and invoice_date. 

Sorry for the long read.

Regards Daryn

Main Sales_data load is;

LOAD

    VKORG,

    num(num#(order_no,'#0')) as order_no,

    order_pos,

    material_no,

    material_name,

    quantity_order,

    price_per_unit,

    unit,

    document_type,

    accounting_type,  

    VTWEG,

    order_date

    sales_office,

    VKBUR,

    invoice_no,

    invoice_pos,

    invoice_date,

    quantity_invoice,

    WAERK_invoice,

    KURRF_invoice,

Round(

  Num(

    If(WAERK_invoice = 'GBP', NETWR_invoice,

    If(WAERK_invoice = 'EUR', NETWR_invoice * KURRF_invoice,

    If(WAERK_invoice = 'USD', NETWR_invoice * (KURRF_invoice))

    ))

  , '#.#############')

, 0.01) AS amountLC_invoice,

realised UA_Sales missing lots of value due to pricing by PO

    quote_no,

    quote_pos,

    quantity_quote,

    Mid(AG_customer,4,11) AS Customer_Number,

    Mid(AG_customer,16,30) AS Customer_Name,

    intercompany,

    export,

    AG_country,

    AG_country_name,

    WE_country,

    EU_zone,

    currency,

    customer_order_no,

FROM [lib://QVD-Generator)/ Sales_data.qvd] 

[hugo_andrade]

Hi @Daryn ,

What you’re describing breaks down into two parts:

Correcting order values by bringing in the header values from VBAK and the exchange rates from VBKD. Join those back to your Sales_data so you always have a reliable “local currency order amount” alongside the invoice values.

Build a Shared/master calendar. Instead of letting either order_date or invoice_date take precedence, normalize both into one calendar. The calendar can carry a DateType flag (Order vs Invoice) so you can filter across all dates, or split the analysis by type when needed.

Seems a lot, but this way you’ll have Sales_data enriched with corrected amounts from VBAK/VBKD. And a single calendar dimension that works consistently for both order and invoice reporting.

If that direction sounds right to you, I can share a simplified load script example showing how to stitch VBAK/VBKD into Sales_data and then build the calendar bridge.

Let me know!

[Daryn]

Hi and many thanks for taking the time to read and reply. That sounds like the perfect way forward, if you don't mind sharing a 'simplified load script example' as mentioned, I would really appreciate it.

Thanks again.

Daryn

[hugo_andrade]

Hi @Daryn ,
Great, here's the simplified script pattern you can adapt. I'm trying to enrich Sales data with correct order values from VBAK and VBKD; then build a master calendar that covers both order and invoice dates. It's hard to test without the files, but I hope it helps! I ran it on ChatGPT for additional checks. Here it is:

// ======================================================
// Qlik Script — Enrich Sales_data with VBAK/VBKD + Shared Calendar
// Final version (single block). Adjust lib paths/field names as needed.
// ======================================================

// Keep numeric parsing consistent
SET DecimalSep='.';
SET MoneyDecimalSep='.';

// ======================================================
// 1) LOOKUPS (MAPPING LOADS) FROM VBAK / VBKD
// Use ApplyMap() later to avoid big joins and row explosion.
// ======================================================

// VBAK: header net value (NETWR) per order
VBAK_Map_NETWR:
MAPPING
LOAD
NUM(NUM#(VBELN,'#0')) AS order_no,
Round(Num#(NETWR,'#.#############'),0.01) AS NETWR_hdr
FROM [lib://QVD-Generator/VBAK.qvd] (qvd)
WHERE fabs(Num#(NETWR,'#.#############')) >= 0.005;

// VBAK: header currency (WAERK) per order
VBAK_Map_WAERK:
MAPPING
LOAD
NUM(NUM#(VBELN,'#0')) AS order_no,
WAERK AS WAERK_hdr
FROM [lib://QVD-Generator/VBAK.qvd] (qvd);

// VBKD: normalize KURSK and aggregate one rate per order
VBKD_Pre:
LOAD
NUM(NUM#(VBELN,'#0')) AS order_no,
Num#(Replace(KURSK,',','.'),'0.0000') AS KURSK_norm
FROM [lib://QVD-Generator/VBKD.qvd] (qvd);

VBKD_KURSK_Map:
MAPPING
LOAD
order_no,
Max(KURSK_norm) AS KURSK_order
RESIDENT VBKD_Pre
GROUP BY order_no;

DROP TABLE VBKD_Pre;

// ======================================================
// 2) FACT LOAD: SALES_DATA ➜ ENRICH & SAFE CALCS
// No self-joins. Compute with ApplyMap() in a derived RESIDENT load.
// ======================================================

Sales_data:
LOAD
VKORG,
NUM(NUM#(order_no,'#0')) AS order_no,
order_pos,
material_no,
material_name,
quantity_order,
price_per_unit,
unit,
document_type,
accounting_type,
VTWEG,
Date(order_date) AS order_date,
sales_office,
VKBUR,

NUM(NUM#(invoice_no,'#0')) AS invoice_no,
invoice_pos,
Date(invoice_date) AS invoice_date,
quantity_invoice,
WAERK_invoice,
KURRF_invoice,
// Include if exists in QVD; otherwise amountLC_invoice will be null (safe):
NETWR_invoice,

// Other descriptive fields
quote_no,
quote_pos,
quantity_quote,
Mid(AG_customer,4,11) AS Customer_Number,
Mid(AG_customer,16,30) AS Customer_Name,
intercompany,
export,
AG_country,
AG_country_name,
WE_country,
EU_zone,
currency,
customer_order_no
FROM [lib://QVD-Generator/Sales_data.qvd] (qvd);

// Derive enriched fields (no joins)
Sales_data_fixed:
NOCONCATENATE
LOAD
*,
// Lookups via ApplyMap (header net/currency/rate per order)
ApplyMap('VBAK_Map_NETWR', order_no, Null()) AS NETWR_hdr,
ApplyMap('VBAK_Map_WAERK', order_no, Null()) AS WAERK_hdr,
ApplyMap('VBKD_KURSK_Map', order_no, 0) AS KURSK_order,

// Corrected ORDER amount in local currency
// ⚠ Verify KURSK direction in your SAP extract and flip * vs / if needed.
Round(
Num(
If(WAERK_hdr='GBP', NETWR_hdr,
If(WAERK_hdr='EUR', NETWR_hdr * KURSK_order,
If(WAERK_hdr='USD' and KURSK_order<>0, NETWR_hdr / fabs(KURSK_order),
Null())))
,'#.#############')
,0.01) AS amountLC_order,

// INVOICE amount in local currency (guard for missing inputs)
Round(
Num(
If(WAERK_invoice='GBP' and IsNum(NETWR_invoice), NETWR_invoice,
If(WAERK_invoice='EUR' and IsNum(NETWR_invoice) and IsNum(KURRF_invoice), NETWR_invoice * KURRF_invoice,
If(WAERK_invoice='USD' and IsNum(NETWR_invoice) and IsNum(KURRF_invoice) and KURRF_invoice<>0, NETWR_invoice * KURRF_invoice,
Null())))
,'#.#############')
,0.01) AS amountLC_invoice,

// Calendar keys (one per business date type)
Hash128(NUM(order_no), 'Order') AS %CalendarKey_Order,
Hash128(NUM(invoice_no), 'Invoice') AS %CalendarKey_Invoice

RESIDENT Sales_data;

DROP TABLE Sales_data;
RENAME TABLE Sales_data_fixed TO Sales_data;

// ======================================================
// 3) SHARED / MASTER CALENDAR (supports Order + Invoice without precedence)
// Use two explicit bridge tables so field names match and associations work.
// ======================================================

// Normalize both business dates into a single staging source
CalendarSource:
LOAD
%CalendarKey_Order AS %CalendarKey,
Date(order_date) AS LinkDate,
'Order' AS DateType
RESIDENT Sales_data
WHERE not IsNull(order_date);

CONCATENATE (CalendarSource)
LOAD
%CalendarKey_Invoice AS %CalendarKey,
Date(invoice_date) AS LinkDate,
'Invoice' AS DateType
RESIDENT Sales_data
WHERE not IsNull(invoice_date);

// Build continuous date island (MasterCalendar) from min/max
TempMinMax:
LOAD
Min(LinkDate) AS MinDate,
Max(LinkDate) AS MaxDate
RESIDENT CalendarSource;

LET vMinDate = Num(Peek('MinDate',0,'TempMinMax'));
LET vMaxDate = Num(Peek('MaxDate',0,'TempMinMax'));
DROP TABLE TempMinMax;

MasterCalendar:
LOAD
Date($(vMinDate) + IterNo() - 1) AS LinkDate,
Year(Date($(vMinDate) + IterNo() - 1)) AS Year,
Month(Date($(vMinDate) + IterNo() - 1)) AS Month,
Dual(Year(Date($(vMinDate)+IterNo()-1)) & '-' &
Num(Month(Date($(vMinDate)+IterNo()-1)),'00'),
Year(Date($(vMinDate)+IterNo()-1))*12 + Month(Date($(vMinDate)+IterNo()-1))) AS YearMonth,
QuarterName(Date($(vMinDate) + IterNo() - 1)) AS Quarter,
Week(Date($(vMinDate) + IterNo() - 1)) AS Week,
WeekYear(Date($(vMinDate) + IterNo() - 1)) AS WeekYear
AUTOGENERATE 1
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

// Two explicit bridges — each matches its key name in Sales_data
CalendarBridge_Order:
NOCONCATENATE
LOAD DISTINCT
%CalendarKey AS %CalendarKey_Order,
LinkDate,
'Order' AS DateType
RESIDENT CalendarSource
WHERE DateType='Order';

CalendarBridge_Invoice:
NOCONCATENATE
LOAD DISTINCT
%CalendarKey AS %CalendarKey_Invoice,
LinkDate,
'Invoice' AS DateType
RESIDENT CalendarSource
WHERE DateType='Invoice';

DROP TABLE CalendarSource;

// ======================================================
// Usage notes (inline, non-executing):
// - Filter with MasterCalendar fields (Year/Month/YearMonth). Toggle Order vs Invoice with DateType from either bridge.
// - Sales_data now carries amountLC_order (from VBAK/VBKD) and amountLC_invoice side-by-side.
// - Verify KURSK operator (* vs /) per your SAP rate convention.
// - Ensure NETWR_invoice exists; if not, adapt the invoice calc to your available fields.
// ======================================================

[Daryn]

Hi Hugo, so many thanks again for your help. I applied that but get hit with

'The following error occurred: Field 'WAERK_hdr' not found'

at the point of adding=

// Calendar keys (one per business date type)
Hash128(NUM(order_no), 'Order') AS %CalendarKey_Order,
Hash128(NUM(invoice_no), 'Invoice') AS %CalendarKey_Invoice

RESIDENT Sales_data;

if I comment that part and the rest of the script out it loads okay up until there (if that makes sense). 

Rightly or wrongly, I corrected that by loading a temp table just for the maps to exist first, as:

// Derive enriched fields (no joins)
Sales_data_temp:
NOCONCATENATE
LOAD
*,
// Lookups via ApplyMap (header net/currency/rate per order)
ApplyMap('VBAK_Map_NETWR', order_no, Null()) AS NETWR_hdr,
ApplyMap('VBAK_Map_WAERK', order_no, Null()) AS WAERK_hdr,
ApplyMap('VBKD_KURSK_Map', order_no, 0) AS KURSK_order
Resident Sales_data;

Sales_data_fixed:
NOCONCATENATE
LOAD

Though that now loads my results are okay until I include the values.

As soon as I add amountLC_order or amountLC_invoice to either of the tables, it errors (calculation too large), even if I filter using the mastercalendar Year/Month/Week to one specific week. Actually as soon as I add any other fields it does the same. Sorry! Hoping the attachments might help you. Again appreciate your time and expertise.