Set analysis: count fields based on other field selection

Wed, 25 Nov 2020 16:16:04 GMT

Hi to everybody.

I explain you this big trouble:

I have a really big dataset with a lot data.

After loading the script I have a really big table that has different fields.

The relevant for what I am not able to do and give me problems are the following:

BigTable:

DateA

TimeA

DateB

TypeOfDefect

...Other fields...

But this table results after a join in the script between the followings:

Table1:

DateA

TimeA

TypeOfProduct

...Other fields...

Table2:

DateB
TypeOfDefect

... Other Fields ...

Table 2 is a subset of Table 1 in terms of ID: i.e. all IDs in table 2 are also in Table 1 and not viceversa.

After exhausting trials and errors, I could not be able to construct some table charts, using Set Analysis, doing the following:

When select one or more DateB, I need to count all IDs whose DateA is equal to DateB.

I explain better: DateB represents the date when a unit is classified as defective, while DateA is the date when the unit is subjected to process A.

I need to count, for each TypeOfProduct and for each TypeOfDefect (in two different table charts of course), the number of defectives (i.e. when TypeOfDefect is not null)/the number of ID which was subjected to process A (i.e. I suppose count Time A or Date A).

I tried with set analysis, but it was impossible for me to find a way to compute this, when I select as filter a DateB, I need that the second part of the ratio counts the IDs subjected to process A, but obviously this date must be DateA=DateB. The problem is DateB in null when the unit is not defective. So the result is that it counts only the A-processed which are defectives, not the good ones.

Given that it is a KPI ratio, I need for example, if I select DateB=12th January 2017, I need to count all the defectives defined in that date, divided by all the IDs subjected to process A on the 12th of Jan. 2017.

Thank you very much for the help.

It is really important.

Bye

Re: Set analysis: count fields based on other field selection

girirajsinh — Sat, 14 Jan 2017 17:49:46 GMT

Hi Emanuele

I find difficult to follow your situation.

Could you share your application or part of it after removing or reducing the data ?

Also would be helpful if you breakdown your problem into short problems and explain us here on what you are looking for.

Thanks

Re: Set analysis: count fields based on other field selection

ziadm — Sat, 14 Jan 2017 22:06:20 GMT

Hi Emanuele

may you should revisit your data model and demormalize your table into one fact table ..

I agree with Vaghela that the question is much broad and may you need to share a sample data with expected output

topic Set analysis: count fields based on other field selection in QlikView

Set analysis: count fields based on other field selection

Re: Set analysis: count fields based on other field selection

Re: Set analysis: count fields based on other field selection