Force numeric values to text in inline tale

gramqlik — Wed, 20 Jan 2021 13:47:09 GMT

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.

Re: Force numeric values to text in inline tale

Ksrinivasan — Wed, 20 Jan 2021 16:54:01 GMT

hi,

MAP:
Mapping LOAD
Product,
"Product Y/N"
FROM SSSS1.xlsx]
(ooxml, embedded labels, table is Apply2);

TAB1:
LOAD
Product,
ApplyMap('MAP',Product,Product) as Product_YES_NO
FROM SSS1.xlsx]
(ooxml, embedded labels, table is Apply1);

product code table:

product code with yes / no table

result with product table with yes / no

ksrinivasan

topic Re: Force numeric values to text in inline tale in App Development

Force numeric values to text in inline tale

Re: Force numeric values to text in inline tale