topic Re: Avoiding Items which doesn't have Vendor Names in QlikView

Avoiding Items which doesn't have Vendor Names

kkkumar82 — Thu, 04 May 2017 16:19:47 GMT

Hi All,

Please find below data

Vendor_Name	Item	Component
AFFYMETRIX INC	301110-01	301110-01
	301110-01	301110-01
AFFYMETRIX INC	301130B	301110-01
BERLIN PACKAGING LLC.	301130B	301110-01
Charter Medical	301130B	301110-01
	301130B	301110-01
	105585	301110-01
	301110	301110-01

In fact I was writing a condition like if(isnull(Vendor_Name), Item) but came to know that for eg 301130B

it should not show up as 301130B has other vendors.

The only output I should get is

105585, 301110 as they are not part of another Vendor

Attaching excel of the same

Re: Avoiding Items which doesn't have Vendor Names

kkkumar82 — Thu, 04 May 2017 16:21:20 GMT

please note that the two expected may have multiple rows but for all of them Vendor Names should be null

Re: Avoiding Items which doesn't have Vendor Names

Anonymous — Thu, 04 May 2017 16:22:46 GMT

if(count(total <Vendor_Name> Item) > 0, ...

if(count(total <Item> Vendor_Name) > 0, ...

Re: Avoiding Items which doesn't have Vendor Names

sunny_talwar — Thu, 04 May 2017 16:26:55 GMT

May be this in set analysis

{<Item = {"=Len(Trim(Concat(DISTINCT Vendor_Name))) = 0"}>}

UPDATE: Assuming you want to show 105585 and 301110

Re: Avoiding Items which doesn't have Vendor Names

kkkumar82 — Thu, 04 May 2017 16:34:30 GMT

Does this work when we have multipe rows for 105585 and 301110 Sunny

Re: Avoiding Items which doesn't have Vendor Names

sunny_talwar — Thu, 04 May 2017 16:36:09 GMT

As long as each of the row has no Vendor_Name for 105585 and 301110, you will see both the items. In general, when a item (even if there are thousands of rows) doesn't have a Vendor_Name associated with it, it should work....

Re: Avoiding Items which doesn't have Vendor Names

Anonymous — Thu, 04 May 2017 16:37:27 GMT

should even work without distinct or?

Re: Avoiding Items which doesn't have Vendor Names

kkkumar82 — Thu, 04 May 2017 16:37:50 GMT

Let me give a try with my data

Re: Avoiding Items which doesn't have Vendor Names

sunny_talwar — Thu, 04 May 2017 16:39:01 GMT

It would... but why complicate by adding multiple values of same vendor name... in fact this might work also (assuming Vendor_Name is null and not contain blank spaces)

{<Item = {"=Count(DISTINCT Vendor_Name) = 0"}>}

Re: Avoiding Items which doesn't have Vendor Names

Anonymous — Thu, 04 May 2017 16:40:20 GMT

makes sense

Re: Avoiding Items which doesn't have Vendor Names

swuehl — Thu, 04 May 2017 16:56:28 GMT

Or without an advanced search:

=Concat({<Item = e({<Vendor_Name = {"*"} >} ) >} DISTINCT Item,', ')

Show all Items that are excluded by selecting all Vendor names.

Re: Avoiding Items which doesn't have Vendor Names

kkkumar82 — Thu, 04 May 2017 17:02:20 GMT

Is this be needed to be include in Quotes sunny

{<Item = {"=Len(Trim(Concat(DISTINCT Vendor_Name))) = 0"}>}

Re: Avoiding Items which doesn't have Vendor Names

sunny_talwar — Thu, 04 May 2017 17:03:43 GMT

Hahahah yes, forgot to add the double quotes

Re: Avoiding Items which doesn't have Vendor Names

sunny_talwar — Thu, 04 May 2017 17:04:12 GMT

Updated

Re: Avoiding Items which doesn't have Vendor Names

kkkumar82 — Thu, 04 May 2017 17:05:50 GMT

Sorry Stefan, I didnt get your point here

Can you elaborate this

Re: Avoiding Items which doesn't have Vendor Names

swuehl — Thu, 04 May 2017 17:20:16 GMT

I was just suggesting an alternative approach using the e() function in set analysis:

Set modifiers with implicit field value definitions ‒ QlikView

The expression should return the two items you are looking for:

105585, 301110

Re: Avoiding Items which doesn't have Vendor Names

Anonymous — Thu, 04 May 2017 17:23:01 GMT

... and I think this is the most efficient approach from performance point of view!

Re: Avoiding Items which doesn't have Vendor Names

kkkumar82 — Thu, 04 May 2017 17:47:33 GMT

Sorry all a small correction , the report is actually Components with no Vendors

I just changed the expression to

Sum({<Component = {"=Len(Trim(Concat(DISTINCT SOURCE_VENDOR_NAME))) = 0"}>}SHIPPED_AMOUNT_USD)

My Dimension are

Item, Component , Component Description

but some how both the expressions are not working and they are giving the Vendors which have components

Re: Avoiding Items which doesn't have Vendor Names

sunny_talwar — Thu, 04 May 2017 17:52:44 GMT

Would you be able to share an example where it isn't working?

Re: Avoiding Items which doesn't have Vendor Names

kkkumar82 — Thu, 04 May 2017 17:53:48 GMT

Sunny's suggestion

{<Item = {"=Len(Trim(Concat(DISTINCT Vendor_Name))) = 0"}>}

Yours

{<Item = {"=Len(Trim(Concat({<Item = e({<Vendor_Name = {"*"} >} ) >} DISTINCT Item,', '))) = 0"}>}

is this what your are suggesting Stefan