topic Re: What function to use to replace numbered key holders with series of values in QlikView

What function to use to replace numbered key holders with series of values

rudywelvaert — Sat, 16 Nov 2024 21:48:16 GMT

Hi,

What function can I use to replace numbered key holders with a series of values.

Example.

UnknownFunction('Number of calls since the {1}th quarter of {2}', 4, 2018)

results in the string: 'Number of calls since the 4th quarter of 2018'

Thanks

R.W.

Re: What function to use to replace numbered key holders with series of values

sunny_talwar — Thu, 29 Nov 2018 16:01:31 GMT

Where exactly are you trying to do this? There might be other ways to do this... but there is no function like the one you mentioned above.

Re: What function to use to replace numbered key holders with series of values

marcus_sommer — Thu, 29 Nov 2018 17:01:45 GMT

Maybe you could use a parametrized variable:

Variable That Acts Like a User-Defined Function

Dynamic Field Expressions with Variables

Variables with Parameters

- Marcus

Re: What function to use to replace numbered key holders with series of values

rudywelvaert — Fri, 30 Nov 2018 12:23:56 GMT

Hi Stalwart1,

Thanks

Yes I could use the Replace function:

SET vStartDatum=Floor(Date( Today(), 'DD/MM/YYYY'));

Replace(Replace('Number of calls since the {1}th quarter of {2}','{1}',Div(Month($(vStartDatum)) - 1 , 3) + 1),'{2}',Year($(vStartDatum)))

But I'm looking for one function, not nesting the Replace function

R.W.

Re: What function to use to replace numbered key holders with series of values

sunny_talwar — Fri, 30 Nov 2018 12:32:21 GMT

Why can't you just use this?

='Number of calls since  the '
&
Div(Month($(vStartDatum)) - 1 , 3) + 1)
&
'th quarter of '
&
Year($(vStartDatum))

Re: What function to use to replace numbered key holders with series of values

rudywelvaert — Fri, 30 Nov 2018 12:37:44 GMT

Because I actually strongly simplified the real issue.
I have up to 15 parameters {1}, {2}, {3}, {4}, {5}, ... {14}, {15} and of the string in which they have to be replaced I have 5 different variations in two different languages.

Re: What function to use to replace numbered key holders with series of values

sunny_talwar — Fri, 30 Nov 2018 12:41:59 GMT

Then look at Marcus's response below. I think variable with parameters should be the answer

Re: What function to use to replace numbered key holders with series of values

marcus_sommer — Fri, 30 Nov 2018 12:44:57 GMT

You mean: 'Number of calls since the {1}th quarter of {2}' is the content of a field-value and {1} and {2} are just placeholder?

Then you could use mapsubstring() to replace all the parameters with a single function. See here the function and an (advanced) example:

MappingFunctions/MapSubstring

Wildcard-Lookup-between-two-tables

- Marcus

Re: What function to use to replace numbered key holders with series of values

rudywelvaert — Mon, 03 Dec 2018 08:01:40 GMT

marcus_sommer,

thanks.

But it looks like this mapsubstring() is used in scripts.

I'm looking for a function used in expressions on text boxes, titles, ...

R.W.

Re: What function to use to replace numbered key holders with series of values

marcus_sommer — Mon, 03 Dec 2018 08:24:37 GMT

I think in this case you will need multiple and/or nested expressions. Like already mentioned you could use parametrized variables to simplify it and keep the "ugly" expression within a variable.

But IMO you should do everything of this transformations within the script or at least the most important parts. I don't see much benefit to do it within the UI. You mentioned it should serve for some kind of adjustment for different languages and/or different formattings and therfore I think the following will be quite useful for you: Handling-Multiple-Languages.

- Marcus

Re: What function to use to replace numbered key holders with series of values

rudywelvaert — Mon, 03 Dec 2018 09:13:20 GMT

Marcus,

The fact that it's needed in the UI is because the user must have the control of several options and these options define the expressions in the text boxes, titles, etc etc

These options concern dates, date periods (day, week, month, quarter, year), language, ...

There is no choice

I'll do it with nested replace functions, I'd hoped for a more transparent solution.

R.W.

Re: What function to use to replace numbered key holders with series of values

marcus_sommer — Mon, 03 Dec 2018 10:02:52 GMT

Please read the above mentioned link again because I think it covered your task quite well. The users could select their wished language per variable or per fieldselections (both ways are possible) and titles, expressions and so on will be appropriate adjusted with the translation table which here worked as a kind of mapping.

If you want to implement multiple independent adjustments like the language translation you could just duplicate the logic to provide the possibility to choose the date- and number-formattings and other thing separately.

Of course UI solutions will be possible but AFAIK the suggested script-approach will be the easiest way to handle it especially if it needs to be applied to multiple applications and if the apps needs to be maintained.

- Marcus