QlikView UOM Conversions Not Working in Table F4111

MarioGB — Fri, 15 Nov 2024 21:09:13 GMT

I'm currently working on a QlikView script to convert quantities from CA to EA and DS in the F4111 table using a mapping table. However, I'm facing challenges with the conversions not producing the expected results. I'm looking for assistance to troubleshoot and resolve this issue. Below, I've detailed my script and included a screenshot of the UOM table for reference.

```qlikview
// Map Net_Quantity_xx to Month names
MonthMap:
Mapping LOAD * Inline [
Net_Quantity_Field, Month_Name
Net_Quantity_01, Jan
Net_Quantity_02, Feb
Net_Quantity_03, Mar
Net_Quantity_04, Apr
Net_Quantity_05, May
Net_Quantity_06, Jun
Net_Quantity_07, Jul
Net_Quantity_08, Aug
Net_Quantity_09, Sep
Net_Quantity_10, Oct
Net_Quantity_11, Nov
Net_Quantity_12, Dec
];

// Load the data from the QVD file and apply the crosstable transformation
CrosstableData:
Crosstable(MonthNumber, Quantity, 16)
LOAD
company_key,
Document_Type,
Fiscal_Year + 2000 as Fiscal_Year,
...
Net_Quantity_12
FROM [D:\JDE\QVDs\Entities\asofbalance.qvd] (qvd);

// Applying MonthMap and Creating AsofData
AsofData:
LOAD
Fiscal_Year,
business_unit_key,
Lot_Serial_Number,
Trim((ApplyMap('MonthMap', MonthNumber))) as MONTH,
Quantity,
CompositeKey
Resident CrosstableData;

DROP Table CrosstableData;

// UOM Conversion Mapping
ConversionMap:
MAPPING LOAD
product_key & '|' & upper(FromUM) & '|' & upper(ToUM) as product_uom_key,
ConversionToPrimaryUOM
FROM [D:\JDE\QVDs\Entities\UOM.qvd] (qvd)
WHERE upper(FromUM) = 'CA' AND upper(ToUM) = 'EA' OR 'DS';

// Loading F4111 and Applying Conversions
F4111:
LOAD
document_type,
document_number,
business_unit_key & '|' & product_key as CompositeKey,
...
product_key & '|' & upper(trans_uom_ledger) & '|EA' as conversion_key,
...
If(upper(trans_uom_ledger) = 'CA',
quantity_puom * ApplyMap('ConversionMap', conversion_key, 1),
quantity_puom) as Converted_quantity_puom
FROM [D:\JDE\QVDs\Entities\inventory_trx.qvd] (qvd)
WHERE Year(trx_date) <= Year(Today());

```

Issue:

Quantities in CA should be converted to their corresponding values in EA and DS based on the ConversionMap table. The Converted_quantity_puom field is expected to contain these converted values.

Questions:

What debugging techniques or tools in QlikView can I use to trace the conversion process and identify the source of the issue?
Could there be a problem with how I'm using the ApplyMap function or constructing the conversion_key?
Are there alternative methods for achieving these UOM conversions within QlikView?
Might there be data quality issues or inconsistencies in the source data or mapping table?
Are there any known limitations or best practices related to UOM conversions in QlikView that I should consider?

Any insights or suggestions would be greatly appreciated.

Re: QlikView UOM Conversions Not Working in Table F4111

jochem_zw — Wed, 03 Jan 2024 17:09:41 GMT

Are you sure this part is working?? there is no error?

this should be:

WHERE upper(FromUM) = 'CA' AND (upper(ToUM) = 'EA' OR upper(ToUM) ='DS');

Re: QlikView UOM Conversions Not Working in Table F4111

kaushiknsolanki — Mon, 08 Jan 2024 10:02:57 GMT

You can make the changes suggested by @jochem_zw or you can try below.

Where Wildmatch(FromUM,'CA') and Wildmatch(ToUM,'EA','DS')

The wildmatch function will disregard the case of the text.

If you need an exact case i.e. upper case you can replace wildmatch to match.

Re: QlikView UOM Conversions Not Working in Table F4111

MarioGabriel — Tue, 26 Mar 2024 09:38:34 GMT

Thank you so much

topic Re: QlikView UOM Conversions Not Working in Table F4111 in App Development

QlikView UOM Conversions Not Working in Table F4111

Re: QlikView UOM Conversions Not Working in Table F4111

Re: QlikView UOM Conversions Not Working in Table F4111

Re: QlikView UOM Conversions Not Working in Table F4111