Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
gramqlik
Contributor III
Contributor III

Force numeric values to text in inline tale

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.

1 Reply
Ksrinivasan
Specialist
Specialist

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:

Ksrinivasan_0-1611161429609.png

product code with yes / no table

Ksrinivasan_1-1611161506042.png

 

result with product table with yes / no 

Ksrinivasan_2-1611161562322.png

 

ksrinivasan