Hi,
Ok, I fixed this issue as I was posting, but I thought I'd continue anyway, as my fix feels like a workaround rather than a proper solution....
The issue is with a Mapping Load of an Inline table of numeric values, which fails to find matches against a text field in the ApplyMap . Here's a basic example:
Products:
LOAD * INLINE [
Product
101
102
103];
ProductMap:
Mapping Load
Product AS [ProductCode],
'Yes' AS [Product YesNo]
RESIDENT Products;
DROP TABLE Products;
LOAD
ApplyMap('ProductMap', [ProductRef], 'No') AS [Product YesNo];
FROM PRODUCTS.qvd (qvd);
The above worked in Qlikview, but no longer works in Qlik Sense.
In QS, when I add the Product YesNo field to a table in my front end, every single record, including those in the Inline table, returns No. My guess is that QS sees the numeric values in the inline table and classes them as NUM, whereas the Product Number field in the source table is TEXT.
I tried lots of variations on applying TEXT() and NUM() formats, but even if I try to format every instance of the field as TEXT, it still doesn't return any Yes values, e.g. this still doesn't work:
Products:
LOAD TEXT(Product) AS ProductText
INLINE [
Product
101
102
103];
ProductMap:
Mapping Load
TEXT([ProductText]) AS [ProductCode],
'Yes' AS [Product YesNo]
RESIDENT Products;
DROP TABLE Products;
LOAD
ApplyMap('ProductMap', TEXT([ProductRef]), 'No') AS [Product YesNo];
FROM PRODUCTS.qvd (qvd);
I finally got it working by adding a prefix letter to the field in both the Mapping Load and to the source field we're comparing against in the ApplyMap. I initially did this by prefixing every value in the inline table with a letter A (which also works), but I then found I could just add the prefix character once, to the field in the Mapping Load, so I now have e.g.:
Products:
LOAD * INLINE [
Product
101
102
103];
ProductMap:
Mapping Load
'A' & Product AS [ProductCode],
'Yes' AS [Product YesNo]
RESIDENT Products;
DROP TABLE Products;
LOAD
ApplyMap('ProductMap', 'A' & [ProductRef], 'No') AS [Product YesNo];
FROM PRODUCTS.qvd (qvd);
So thankfully, this fixes my issue, although it feels like a bit of a wonky workaround. Can anyone let me know the 'proper way' to get Qlik to treat those inline numeric values as text?
Cheers.
