topic Re: How to get subscripts from an Excel column to appear in QlikView? in QlikView

How to get subscripts from an Excel column to appear in QlikView?

pgkrsk — Fri, 16 Jul 2021 06:07:08 GMT

Hi,

I have an Excel with some chemical elements and their values, but when I load that in edit script all the subscripts of the elements gets converted to normal strings (like NO_2 -> NO2), how could I preserve the subscripts from that Excel to appear in QlikView as well? I know I can use ASCII to use subscripts but I can't use it when showing the columns in tables etc.

Re: How to get subscripts from an Excel column to appear in QlikView?

PiEye — Fri, 16 Jul 2021 09:03:08 GMT

Hi Pgkrsk

I've just tried to replicate this, and while I am able to generate subscripts typing directly in Qlik, it seems that they do not import correctly in excel.

I know in Excel you can create the subscript by applying formatting, which makes me wonder if excel saves the formatting, rather than the unicode symbol.

Writing the subscript using an actual character seems to solve the problem - below is an example loaded into qlik - "X2" is using the actual character. "X3 Y4" were created using excel formatting which confirms my suspicion!

So you now have 3 options - either use the correct character in your excel input, use a lookup table to match the symbols to the correct representation or you can use a Qlik mapping function to convert all numbers to subscript:

MapSubScript:
MAPPING LOAD * INLINE [
InputNumber,OutputSubscript
1,₁
2,₂
3,₃
4,₄
5,₅
6,₆
7,₇
8,₈
9,₉
0,₀
];

LOAD
subs,
MapSubString('MapSubScript', subs) as MappedSubScript
FROM [$(vFile)] (ooxml, embedded labels, table is Sheet1);

Here I've used MapSubString to pick out any numbers and replace them with their subscript equivalents.

This is the result:

HTH

Re: How to get subscripts from an Excel column to appear in QlikView?

marcus_sommer — Fri, 16 Jul 2021 11:48:07 GMT

What happens if you use: text(MyField) as myField

- Marcus

Re: How to get subscripts from an Excel column to appear in QlikView?

PiEye — Fri, 16 Jul 2021 12:10:00 GMT

I tried replying earlier, but it seems my response didn't save for some reason.

Essentially Excel is using formatting to define the subscripts, and not the actual ascii . UTF character.

In this case, we have a few options:

1 - ensure that you use the actual character and not formatting in excel, to apply the subscript

2 - create some sort of lookup table in excel

3 - create a mapping table to convert all numbers and apply this using mapsubstring:

MapSubScript:
MAPPING LOAD * INLINE [
InputNumber,OutputSubscript
1,₁
2,₂
3,₃
4,₄
5,₅
6,₆
7,₇
8,₈
9,₉
0,₀
];

LOAD
subs,
MapSubString('MapSubScript', subs) as MappedSubScript
FROM [$(vFile)] (ooxml, embedded labels, table is Sheet1);

In the above example I have loaded a table from excel with both a formatted subscript (X3 Y4) and a "character subscript" (X2) and applied the mapping. This is the result. as you can see the character subscript ingests fine, but qlik wont apply the formatting from excel

Hope this helps