topic Re: Calculated dimension if condition in QlikView

Calculated dimension if condition

marcelo_7 — Tue, 18 Sep 2012 13:36:12 GMT

Hello,

I have a table containing a list of partnumbers, supplier of that part number, to what store and the vendor partno which is different from ours.

In this case two suppliers deliver three different partno but we sell it as the same. As you can see in the example below one supplier has two part numbers for our part no and we want to catch those errors.

Partno Store Supplier Sup.Partno

1000 1 A 37

1000 2 A 37

1000 1 A 38

1000 1 B 76

i have a pivot table

with the dimensions

Partno

Supplier

Sup.Partno

and the expression

count(Sup.Partno) because i want to see how many occurences there are (=how many stores have that partno)

In my pivot table i want to show only suppliers that have two partno for one of ours and not show the others.

By checking out the community I thought that my dimension expression would work but it didn't and I would like some help.

Dimension Supplier:

aggr(if(count(distinct Sup.Partno)>1, Supplier, null()), Supplier)

Re: Calculated dimension if condition

whiteline — Tue, 18 Sep 2012 13:46:35 GMT

I thought that my dimension expression would work but it didn't

It seems it works.

Just toggle 'Supress when value is null' checkbox.

Re: Calculated dimension if condition

marcelo_7 — Tue, 18 Sep 2012 14:12:30 GMT

I must have missed to include another field that messes it up. Because when I do this example myself it works.

Re: Calculated dimension if condition

marcelo_7 — Tue, 18 Sep 2012 15:17:08 GMT

I have attached a qvw for you to try. In the right one "A with aggr()" all are visible. But if you click 0101 it shows nothing because the expression doesn't apply after the selection. But according to me it doesn't apply before but it still shows all Partno. I'm missing something. I also included the an extended version of the inline example for you convenience.

Thanks in advance.

Re: Calculated dimension if condition

whiteline — Tue, 18 Sep 2012 17:11:26 GMT

As you want select vendors having more than one vendorPartNo in general (not in current selection), correct your set analysis expression:

=aggr(if(count({1<Active={1}>} distinct vendorPartNo)>1, vendor, null()), vendor)

Re: Calculated dimension if condition

marcelo_7 — Wed, 19 Sep 2012 07:31:49 GMT

I tried adding the 1 and also tried with $1 but neither of them work properly. All are shown and when clicking either partno the partno is shown even though only 1005 should be shown.

Re: Calculated dimension if condition

whiteline — Wed, 19 Sep 2012 08:33:54 GMT

I think I've missed something... why only 1005 ?

Re: Calculated dimension if condition

marcelo_7 — Wed, 19 Sep 2012 08:41:27 GMT

Because 1005 has two vendor part numbers for one of ours. And we don't want doubles. It would be problematic to if we bought two different products from a vendor and sold them as the same. Therefore I want to catch the suppliers who have several vendor_part_no for one of our partno.

Re: Calculated dimension if condition

whiteline — Wed, 19 Sep 2012 09:43:20 GMT

In this case I guess you use the wrong logic.

As I understand you want to check vendorPartNo count for every pair vendor-partNo

I think this should work:

=aggr(if(Max(aggr(count({1<Active={1}>}distinct vendorPartNo), vendor, partNo))>1, vendor, null()), vendor)

Re: Calculated dimension if condition

marcelo_7 — Wed, 19 Sep 2012 10:27:56 GMT

It works for the example qvw but when I apply it to the original data partno that don't apply show up as results. I'll have to dig deeper. Thanks for taking the time helping me though!