topic Re: Keep original values visible after selection in QlikView

Keep original values visible after selection

pvanderkolk — Tue, 10 Apr 2018 08:48:28 GMT

Dear Qlikers,

In the attached Excel sheet you find three straight tables with a different selection. It is showing the inventory on articles where a customer has put an order against. When I'm selecting a Containerno I get extra stock (see column Container) it logically shows a value and amount for the articles that are available in the selected container, like the second table Output.

However, I want my straight table as shown in the below straight table Desired output. So the original expression values shouldn't dissapear and the extra stock from the container should also be visible in the columns Value and Deliverable. I'm using below formula and it works when I don't select any container, but will turn into the second table in the Excel attachment when I do select a container.

=Sum(Aggr(

If(sum({<ArtHasInventory={1}>}OrdAmountOrdered-OrdAmountDelivered)<=

If(GetSelectedCount(Containerno)>=1,sum({<ArtHasInventory={1}>}Container),0)+

sum({<ArtHasInventory={1}>}if(Stock<0,0,Stock)),

sum({<ArtHasInventory={1},Containerno=>}(OrdAmountOrdered-OrdAmountDelivered)*(Price-(Price*Discount/100)),

(If(GetSelectedCount(Containerno)>=1,sum({<ArtHasInventory={1},Containerno=>}Container),0)+sum({<ArtHasInventory={1},Containerno=>}if(Stock<0,0,Stock)))*(Price-(Price*Discount/100)))

,Order,Article))

OrdAmountOrdered-OrdAmountDelivered is the formula of the column To Deliver.

Is this problem solvable (without using Alternative State)?

Thanks in advance!

Pim

Re: Keep original values visible after selection

sunny_talwar — Tue, 10 Apr 2018 09:03:27 GMT

Would it be possible to share a sample app to trouble shoot this issue?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Keep original values visible after selection

olivierrobin — Tue, 10 Apr 2018 09:22:12 GMT

hello

and what about using identifier 1 in your set analysis so they don't take care of your current sélections ?

i.e : sum({1< ...

Re: Keep original values visible after selection

pvanderkolk — Tue, 10 Apr 2018 10:45:53 GMT

Hi Sunny,

See attached. Thanks in advance!

Re: Keep original values visible after selection

pvanderkolk — Tue, 10 Apr 2018 10:47:51 GMT

Hi Olivier,

Yes, I've tried that as well.. But unfortunately I've ended up with the same problem that it only showed the values of the selected container and removed the rest

Re: Keep original values visible after selection

sunny_talwar — Tue, 10 Apr 2018 10:51:10 GMT

We are trying to fix the Deliverable expression, right?

Re: Keep original values visible after selection

sunny_talwar — Tue, 10 Apr 2018 10:58:19 GMT

Containerno is not available for all Article here

Is that how the data is? What is the expected output

I posted a screenshot, let me know if you would like me to delete it.

Re: Keep original values visible after selection

pvanderkolk — Tue, 10 Apr 2018 11:01:16 GMT

Deliverable and Value. But I guess it's the same issue. So if we solve the one, we have solved the other

Re: Keep original values visible after selection

pvanderkolk — Tue, 10 Apr 2018 11:04:09 GMT

So the containers (shown as containerno in listbox) should increase the value/deliverable when selected, but also show the original values (instead of disapearing).

In the example: the total value should increase with 480 euros and we should be able to see the three new values in the table together with the original values.

Re: Keep original values visible after selection

sunny_talwar — Tue, 10 Apr 2018 11:09:52 GMT

The Value disappears because there are a lot of Article where there is no container information available. In my screenshot you will see that there are only 6 articles with container information... is this how you expect the data to be?

Re: Keep original values visible after selection

pvanderkolk — Tue, 10 Apr 2018 11:16:43 GMT

Yes Sunny, only those six articles are available in the two containers. The field isn't a column in the table, but will only be used as a list box. But when selecting (one of) the containers, the value and deliverables should increase and be added to the totals that are covered by the warehouse stock

Re: Keep original values visible after selection

sunny_talwar — Tue, 10 Apr 2018 11:36:49 GMT

I am not entirely sure I understand your business... but is this what you want?

Without selection

With selection

Re: Keep original values visible after selection

pvanderkolk — Tue, 10 Apr 2018 11:52:19 GMT

Yes, the far right column is doing what the columns Value and Deliverable have to do too: when you select container 55685... the three articles in that container change from 0 to the new value. The articles that aren't in one of the containers should't change and must retain their values. When I added the If-function to the column far right, I got the same issue as in the columns Value and Deliverables..

Re: Keep original values visible after selection

sunny_talwar — Tue, 10 Apr 2018 11:59:16 GMT

Sorry, my bad... I inquired about the wrong expression... but you description to my incorrect inquiry helped a lot (I think)... is this what you want?

Re: Keep original values visible after selection

pvanderkolk — Tue, 10 Apr 2018 12:08:56 GMT

Almost Sunny! I already had the same output for the column far right, that's why I've added that to my sample as well. But the two target columns are getting closer the correct answer as well! The only thing is that it shows the new values for both containers, even if select only one container. In the latter case only three values should increase instead of six.

Re: Keep original values visible after selection

sunny_talwar — Tue, 10 Apr 2018 12:32:02 GMT

How about now?

Re: Keep original values visible after selection

pvanderkolk — Tue, 10 Apr 2018 12:51:03 GMT

As usual you've hit the nail on the head! This was exactly what I needed Sunny!

Played a lot with the field Containernummer in the Set Analysis, but I couldn't execute it with the right result. Besides that I haven't put this field between the Sum and Aggr function, so I was doomed to fail anyways

Could you please remove your two qvw's from the post (no password needed anymore).

Thanks again for the great help Sunny, much appreciated!!

Re: Keep original values visible after selection

sunny_talwar — Tue, 10 Apr 2018 12:52:44 GMT

Qvw removed... images can stay?

Re: Keep original values visible after selection

pvanderkolk — Tue, 10 Apr 2018 13:06:33 GMT

No problem!