Qlik Community

Ask a Question

Integration, Extension & APIs

Discussion board where members can learn more about Integration, Extensions and API’s for Qlik Sense.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
berndpodhradsky
Partner
Partner

NxCell Number/Date Format String

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

6 Replies
yko
Employee
Employee

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.

 

berndpodhradsky
Partner
Partner
Author

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

yko
Employee
Employee

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 🙂

berndpodhradsky
Partner
Partner
Author

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
yko
Employee
Employee

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).

berndpodhradsky
Partner
Partner
Author

Hi!

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

Bernd