Copy Selections from one field to another

ChristofSchwarz — Fri, 07 Mar 2014 14:47:19 GMT

I often hear from QV Designers that they want to transfer a selection from one field to another (provided that those two fields have the same values in common). Or similary, to transfer the selections of just one given field from one state to another.

Both is possible.

There is a simple and readable approach which is good to transfer just a couple of dimension values ... and there is a formula to transfer even millions of selections in a second or so.

Assuming you have a field named "FieldCopyFrom" and a field called "FieldCopyTo" (replace this with something out of your model)

COPYING JUST A FEW SELECTIONS FROM ONE FIELD TO ANOTHER

The syntax to set (a small number of) multiple values into a field is this

(value1|value2|valueN)

Separate values with Pipe | and put brackets around. However, this syntax supports wildcards (that's good) but does not support spaces in the values (that's bad). But if you put a single-character wildcard "?" instead of a space " " you can achieve very likely the desired results.

Example:

(Christof?Schwarz|Abraham?Lincoln|Jimmy?Hendrix)

By the way, the same syntax is supported anywhere in the frontend when you type to search for fields! Use * and/or ? wildcards, the pipe acts like an OR. Below example means: All codes containing the combination UT or SA:

To produce this by an Action (Button, Textbox) put the following

Value: ='(' & Concat(DISTINCT Replace(FieldCopyFrom,' ','?'), '|') & ')'

COPYING MASS SELECTIONS FROM ONE FIELD TO ANOTHER

Use the AGGR Function with one dimension to create a on-demand cube (a "vector" of values), and pass this to the target field. The AGGR function has the dimension "FieldCopyTo" and the simpliest Aggregation function Only() with a set-modifier inside. The set operation is the key here: <FieldCopyTo=P(FieldCopyFrom)> .... the rest is nerd syntax 😉

Only disadvantage is, that the FieldCopyTo will only further narrow down the selection if you do not reset the selection first. So you should put two actions: "Clear Selection" of field "FieldCopyTo" first and then this action:

Field: =If(GetSelectedCount(FieldCopyFrom), 'FieldCopyTo')

Value: ='=FieldCopyTo=Aggr(Only({<FieldCopyTo=P(FieldCopyFrom)>} FieldCopyTo), FieldCopyTo)'

The If(GetSelectedCount(..)) condition is good to check, if there was any selection made in FieldCopyFrom. If you don't check this and the action executes, you will see all values selected in FieldCopyTo even when nothing was selected in FieldCopyFrom.

COPYING SELECTIONS OF ONE FIELD FROM ONE ALTERNATE STATE TO ANOTHER

The approach and the syntax to copy the selections of just a single field from one state to another is similar to the above with a slightly adopted syntax, as two different alternate states are now involved and the field name is (assumed) to be the same.

If you copy from the main state into an alternate state, this is the syntax (the essetial part herein is $::CopyField )

Value:='=CopyField=Aggr(Only({<CopyField=P($::CopyField)>} CopyField), CopyField)'

If you copy from an alternate state to another, this is the syntax (the essetial part herein is [SourceState]::CopyField )

Value:='=CopyField=Aggr(Only({<CopyField=P([SourceState]::CopyField)>} CopyField), CopyField)'

Don't forget: Start the action block with a "Clear Field" action first, as otherwise the selection of that "=Aggr..." function can only make the previous selection more narrow but not wider!

Both Actions need to be set to work in the "TargetState", in my case "State2"

If you don't want the user to wonder about the strange formula in the "Current Selection Box" and to start questioning which drugs you took to write it, put a third action after this: "Select Possible". This will resolve the formula into somehing like this

---

Feel free to download the my attached example, where all this code is presented and can be tested

Enjoy

Christof Schwarz

Re: Copy Selections from one field to another

Fri, 07 Mar 2014 15:15:59 GMT

Christof,

That is excellent. Why not create a document? Rather than a discussion that will be "forgotten" in few days ? In other words, is is the right place ?

I did not know the trick with aggr().

Fabrice

Re: Copy Selections from one field to another

ChristofSchwarz — Fri, 07 Mar 2014 15:51:13 GMT

Good. Point. I don’t know how the “document” section is organized. I keep creating posts in the default area and this is “discussion” … Can I still move this content?

topic Re: Copy Selections from one field to another in QlikView

Copy Selections from one field to another

Re: Copy Selections from one field to another

Re: Copy Selections from one field to another