topic Re: NxCell Number/Date Format String in Integration, Extension & APIs

NxCell Number/Date Format String

berndpodhradsky — Tue, 17 Dec 2019 01:00:59 GMT

Hi!

When reading a hypercube through a pager using the .NET SDK, I successfully get arrays of NxCells that contain Num and Text properties (basically using the code given here: https://github.com/kolsrud/qlik-dot-net-sdk-hypercube-usage). However, what I couldn't find out is: how to get the custom number format that has been applied to a value.

In scenarios where there's a "global" number format set in the measure definition, it's easy; the measure definition then contains all the information needed.

But what if the measure is set to "AutoFormat" and the measure expression contains something similiar to "if(<condition>, num(<val1>, <format1>), num(<val2>, <format2>))"? In such a case, there is not just one format string per measure and each NxCell could (potentially) have its own format string.

Is there any way to get access to the effective format string applied to a given NxCell?

Kind Regards,
Bernd

Re: NxCell Number/Date Format String

Øystein_Kolsrud — Tue, 17 Dec 2019 08:23:37 GMT

I'm pretty sure the answer to your question is "no". In your case the format is baked into the expression it self, and as far as I know there is no way to extract such metadata from an evaluated expression. But you could add another measure to the hypercube that only contains the format. That way you could correlate the resulting number with the format by associating the two columns of the resulting table. So one measure would be:

if(<condition>, num(<val1>, <format1>), num(<val2>, <format2>))

and the other measure would simply be:

if(<condition>, <format1>, <format2>)

Each row of the resulting table would then contain both the computed value and the format used.

Re: NxCell Number/Date Format String

berndpodhradsky — Tue, 17 Dec 2019 12:48:26 GMT

Hi!

Thanks for your answer. Unfortunately that's not a solution that would work in our context, because it would require massive rework of many tables and also leads to additional columns visible to the user.

So essentially what you're saying is that there's no reliable, bullet-proof way of reading NxCells and get their correct format, because you never know if an expression has explicit format strings set and that information is not available to API consumers? Are there any plans to change that?

Kind Regards,
Bernd

Re: NxCell Number/Date Format String

Øystein_Kolsrud — Wed, 18 Dec 2019 08:06:47 GMT

Hmm... What's the context in which this is going to be used? Are you looking for a generic solution to automatically get the format for any measure? In that case I agree that there is no good solution.

But my suggestion does not really require you to change the app at all. As you are interacting through the API you could simply create a session object with a copy of the hypercube and add the extra measure there. However, that suggestion is based on my original assumption that you know in advance exactly which measure you want to retrieve the format from and the condition for choosing that formatting. And if that assumption is false, then I'll keep quiet 🙂

Re: NxCell Number/Date Format String

berndpodhradsky — Wed, 18 Dec 2019 08:15:20 GMT

Hi!

I could add the additional measure through the API, that‘s correct. But with which expression? I would have to dynamically parse the expression of the actual measure and then reconstruct it for the additional measure. If an app designer changes the original measure, my format measure would otherwise be incorrect.

Basically it should be a general approach to circumnavigate problems with the current Excel export, where values are being exported to Excel as text rather than a number + a format string. But what if you want to make further calculations in Excel? Not possible if the values are texts...

Kind Regards,
Bernd

Re: NxCell Number/Date Format String

Øystein_Kolsrud — Wed, 18 Dec 2019 08:23:10 GMT

I hear you. That's the "generic solution" solution I mentioned in my previous comment. There is no solution for that (as far as I know at least).

Re: NxCell Number/Date Format String

berndpodhradsky — Wed, 18 Dec 2019 08:26:20 GMT

Hi!

That‘s what I thought. Well, thanks for your time and help!

Bernd