topic Re: how to select only one element based on another selection in QlikView

how to select only one element based on another selection

Anonymous — Tue, 23 Dec 2014 13:40:36 GMT

Hi, I have a problem to create a button select an element from the result of another selection.

Target:

1. Select the latest "Date".

2. Based on the latest Date, the column Name can have multiple result.

3. Select only one Name from step 2.

Is there some thing like below:

somefunction({<Date={'$(=only(max({<Date={"<=01-10-2014 0:00:00"}>}Date)))'}>}Name)

that can select only one (the top one) Name from the Name list?

This is like to use array[1] in php, but I do not know how to select it in qlikview.

Can any one help?

THanks very much

Re: how to select only one element based on another selection

german_avanzato — Tue, 23 Dec 2014 13:49:09 GMT

Hi,

If you need the "Name" field, one way could be a combination of aggr function with FirstSortedValue

FirstSortedValue(Name, -aggr(sum/max/rank({<Date={"<=01-10-2014 0:00:00"}>value),Name))

The expression is only an approach.

Re: how to select only one element based on another selection

Anonymous — Tue, 23 Dec 2014 14:18:28 GMT

@German,

Sorry, I made a mistake. It still dose not work.

It works only when the max(Date) and Name are 1-1 match. If for a max(Date), there are multiple value in Name column. Then the result is null.

Re: how to select only one element based on another selection

german_avanzato — Tue, 23 Dec 2014 16:15:12 GMT

If you could upload and example of your model, maybe I can solve the problem.

aggr function is like a group by function.

Maybe, you need to add the Date into the dimmension

FirstSortedValue(Name, -aggr(sum/max/rank({<Date={"<=01-10-2014 0:00:00"}>value),Date,Name))

and/or use the rank function, to sort the result/limit it.

With and example of your model will be much easy to help you.

Re: how to select only one element based on another selection

Anonymous — Mon, 05 Jan 2015 11:31:09 GMT

Hi, All

Happy 2015!!

I still have this problem. My target is to have two buttons: 1st button is to select a Date (Step 1 in the example), and then run a macro to press the 2nd button to select the Name for the Name list box(Step 2 in the example ).

And attached is a example .qvw file.

Case 1:You can see when you click on button "SelectDateOneName", and then click on button "SelectName", it works.

Case 2:But when you "SelectDateMultiName", then my solution dose not work.

I am wondering in case 2, how can I select all result and one result (only Name1, first one order by alphabet) in the "Name list box".

Thanks very much

Re: how to select only one element based on another selection

Anonymous — Mon, 05 Jan 2015 12:39:59 GMT

Hi, German,

Thanks for all your help. And happy new year.

I attached an example .qvw file.

Zhihong

Re: how to select only one element based on another selection

rubenmarin — Mon, 05 Jan 2015 13:03:09 GMT

Hi, for the Select Name Button you can select:

=MinString({<Date={'$(=only(Date))'}>}Name)

Re: how to select only one element based on another selection

Anonymous — Mon, 05 Jan 2015 13:11:45 GMT

Hi, Ruben,

Thanks very much. Your suggestion works to select one Name out.

Do you know in case 2, how can I have all Name selected (Both Name1 and Name2)?

Thanks

Re: how to select only one element based on another selection

ecolomer — Mon, 05 Jan 2015 13:15:31 GMT

Here you have an example.

When I select a project auto selected date max in other variable

Re: how to select only one element based on another selection

rubenmarin — Mon, 05 Jan 2015 13:21:17 GMT

Hi, to select all names you can use:

='('&Concat({<Date={'$(=only(Date))'}>}Name,'|')&')'

Re: how to select only one element based on another selection

Anonymous — Mon, 05 Jan 2015 13:43:48 GMT

Hi, Enrique,

Thanks for your finding.

I do not know why it behaves like this when you select "project", then the max "date" is selected.

Normally, when you select a project, then the available date should be highlighted (available to be selected), but not selected. You need to click to select it or use button to select it.

If you try the other way around(and that is my case, select the latest Date to get the Name), first select a "date", and you will see the the the "project" is not selected. This is a normal behave in qlikview.

Zhihong

Re: how to select only one element based on another selection

ecolomer — Mon, 05 Jan 2015 13:49:48 GMT

Hi Zhihong

This was an example I have given him because he believed he could help.

If what you want is upside down, select a date and see which names are associated, this QV is offered in standard form.

I've probably misunderstood your requirement, because my English is not very good.

If I explain in more detail try to help you.

Re: how to select only one element based on another selection

Anonymous — Mon, 05 Jan 2015 14:00:57 GMT

Hi, Ruben,

Thanks so much.

It works. I improve it a little bit as there are space in the Name in my real project.

='('&'"'& Concat({<CaStartDate={'$(=only(CaStartDate))'}>}CampaignName,'"|"')&'"'&')'

Zhihong

Re: how to select only one element based on another selection

Anonymous — Mon, 05 Jan 2015 14:04:52 GMT

Hi, Enrique,

My problem solved.

Your answer helps me to know a new feature of QV.

Thanks very much for your time.

Zhihong