topic Re: Comma Issue - Dollar Expansion with Parameters in QlikView

Comma Issue - Dollar Expansion with Parameters

Jagsfan82 — Sat, 16 Nov 2024 03:36:53 GMT

Hello,

I'm having issues with using single quotes and commas when passing parameters into variables.

Essentially I have an expression with set analysis I want to reuse across my document, and I want to be able to pass additional set analysis elements, ideally stored in variables, into it. Sometimes those additional set analysis elements contain commas or single quotes (e.g. Field={'020','030'}).

My original Expression I want to modify is:

sum({$<$1,$2,[Insurance Type Desc]={'Direct','Assumed'}>} [Written Premium])

Additional set analysis I may want to pass in is:

[Policy Key]={"=Count({$<[Transaction Code]={'045','02'}>} [Transaction ID])>0"}

and/or

[Transaction Code]={'00','01','010','020'}

So I want to be able to do something like:

$(vSumGrossWP_Mod($(vE21Set),$(vE21Set2)))

I have looked at a few other posts and tried some solutions and couldn't get anything to work. I've tried both storing the additional parameters in variables and typing them directly. I understand the concept of replacing the characters, but everything I try is resulting in one of the following:

sum({$< or

sum({$<$1,$2,[Insurance Type Desc]={'Direct','Assumed'}>} [Written Premium]) or

sum({$<-,[Insurance Type Desc]={'Direct','Assumed'}>} [Written Premium])

Below are links to the topics I have browsed. Any help is appreciated. I would prefer not to upload scrambled example data, but I probably could.

Comma-problem-workaround-for-dollar-sign-expansion

Set-analysis-dynamic-number-formatting-and-commas

nested-variable-troubles

Re: Comma Issue - Dollar Expansion with Parameters

rubenmarin — Sun, 12 May 2019 09:19:28 GMT

Hi, one way to avoid commas is change it by additions of values:
[Transaction Code]={'00'}+{'01'}+{'010'}+{'020'}

Re: Comma Issue - Dollar Expansion with Parameters

marcus_sommer — Mon, 13 May 2019 11:27:34 GMT

Beside the suggestion from Ruben you could try the following syntax:

[Transaction Code]={(00|01|010|020)}

- Marcus

Re: Comma Issue - Dollar Expansion with Parameters

Jagsfan82 — Mon, 13 May 2019 13:51:20 GMT

Thank you both for the suggestions. Ruben's solution worked for my situation.

The pipes wanted to give me a syntax error as if I hadn't completed the field's set. Didn't fidget with it to much. Maybe "=01|02|03|04" would have worked?

Another quick question... I'm assuming if I want to distinguish 002 from 02 I have to enclose it as a string with the single quotes correct? I.E. would I need to type: Field={'02','002'} or would Field={02,002} work as well?

Re: Comma Issue - Dollar Expansion with Parameters

Jagsfan82 — Mon, 13 May 2019 13:53:31 GMT

To be clear, I wouldn't actually be coding 02 & 002 together, it would be more coding one or the other, hoping "Field={01,02}" wouldn't also capture 001 and 002.

Re: Comma Issue - Dollar Expansion with Parameters

rubenmarin — Mon, 13 May 2019 13:56:40 GMT

If it's a text both should work, 2 is a different value than 02 and both are different than 002.