topic Re: Comparing Two FieldsUsing Set Analysis in QlikView

Comparing Two FieldsUsing Set Analysis

Anonymous — Thu, 21 Jun 2018 17:46:05 GMT

Hi all,

I am a beginner to QV so forgive me if this is a simple solution.

I need to use 4 fields with this chart:

MASTER: the number identifying a set of components

COMP: the number identifying the component

QTY: the amount of each component we have in stock

RQTY: the required number of a component needed to make the set

I need to know for each MASTER, whether we have enough stock of each component to make a full set.

The expression I am using right now is the following:

If(count({$<[QTY] = {">=$([RQTY])"}>}[SKU]) = count(SKU), 'Yes', 'No')

However this isn't fully working properly. If we are completely out of stock in a component then it works properly, but when we have stock, but less than the required it outputs 'Yes' instead of 'No'. Does anyone know how I should write the Set Analysis so it catches this properly?

Best,

Kevin

Re: Comparing Two FieldsUsing Set Analysis

sunny_talwar — Thu, 21 Jun 2018 17:54:33 GMT

Would you be able to share few rows of data with the expected output from the sample data provided?

Re: Comparing Two FieldsUsing Set Analysis

Anonymous — Thu, 21 Jun 2018 18:41:32 GMT

sorry the name of the components are SKU, not COMP

Re: Comparing Two FieldsUsing Set Analysis

Anonymous — Thu, 21 Jun 2018 18:54:04 GMT

Here are the two sample input tables:

SKU	QTY
1234	1
2345	5
3456	0
4567	2
5678	1

MASTER	SKU	RQTY
987	1234	1
987	2345	2
987	4567	1
876	5678	2
765	3456	4
765	1234	1

And the desired output:

MASTER	Creatable?
987	Yes
876	No
765	No

Re: Comparing Two FieldsUsing Set Analysis

sunny_talwar — Fri, 22 Jun 2018 02:48:35 GMT

May be this

If(Sum(QTY) > Sum(RQTY), 'Yes', 'No')

Re: Comparing Two FieldsUsing Set Analysis

Anonymous — Fri, 22 Jun 2018 13:18:44 GMT

This doesn't work, this will sum the quantities of all the components and compare it to the sum of all the required quantities. I need it to look at each SKU individually. I am pretty sure you need set analysis to do this.

Re: Comparing Two FieldsUsing Set Analysis

sunny_talwar — Fri, 22 Jun 2018 16:01:04 GMT

Your two tables already join on SKU... are they not?