Format dimension Inline table script

[Brip51]

I have a QV report that I inherited.  It uses an Excel template to read in the left side row labels and the expressions for each cell.  Then there is an inline table

This is the excel template-

Then the inline table -

LOAD

IF(Match(RowNo(), 1, 4, 6, 10, 15, 17, 20, 22, 26, 29, 31, 34), '<b>') AS _Formatting,

RowNo() AS _Index,

_Metric,

_ExpectedPVCashFlow,

_Expression2,

_Expression3,

_Expression4,

_Expression5,

_Expression6,

_Expression7,

_Expression8,

_Expression9,

_Expression10,

_CSMLessLRC,

_Expression11,

_Expression12,

_Expression13

FROM $(v_ExcelSource)ToaRe Tracking IFRS PAA.xlsx

(ooxml, embedded labels, table is IFRSRD);

Then the table uses _metric as a dimension.  Here is the QV report

I am trying to format the row labels with indentation.  I can make them bold by including

<b> in the _Format column.

I want to indent particular rows like such –

CHANGES IN THE STATEMENT OF PROFIT OR LOSS & OCI

Modified Retrospective

Fair Value

Other

Insurance Revenue

Incurred claims and other insurance service expenses

Amortization of insurance acquisition cash flows

Losses and reversal of losses on onerous contracts

Adjustments to liabilities for incurred claims

Insurance Service Expense

Investment components

Insurance Service Result

 

I have tried to use the format column and &nbsp to insert spaces, but that did not work

Is this possible to do ?

Any thoughts on this would be greatly appreciated.

Thanks,

Brian

[mpc]

Hi,

Did you try to add Chr(9), 9 is the ASCII code for "Horizontal tabulation" ?

Kind regards

[Brip51]

Hi,

I just gave it a try and it did not work.

Thanks,

Brian

[marcus_sommer]

AFAIK chr(9) isn't supported as content of a field-value and is replaced with a space of chr(32). Without specifying the system-variable verbatim leading spaces are removed during the load. An alternatively to chr(32) might be chr(160) or another space-chars.

[Brip51]

Hi Marcus,

Thanks.  I tried chr(160)  and that did not work either.  

IF(Match(RowNo(), 1, 4, 6, 10, 15, 17, 20, 22, 26, 29, 30, 33), chr(160)) AS _Formatting,
RowNo() AS _Index,

I tried with and without single quotes. 

I have seen similar formatting (underlining, Italic... ) in the styling here -

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Visualizations/Visualiza...

I can get those to work.  I can use <center> and that will center the text.

Still trying to work this out.

Thanks again for the help,

Brian

[marcus_sommer]

It wasn't meant as a kind of a formatting else as an adjustment of the field-values and may look like:

repeat(chr(160(), Level * 2) & MyDimensionField as DimensionAdjusted

whereby Level is an extra field within your Excel as simplification to avoid n if-loop or pick(match()) queries against the rowno() to define the indent for each value.