topic Selection logic / Set Analysis in QlikView

Selection logic / Set Analysis

Fri, 10 Jul 2015 10:37:39 GMT

I’ve a doubt that i don’t know if QV is able to solve with it’s logic. I’m attaching a QVW to show it.

My purpose is that if i select the value 5 and 9 of the field NIVEL3 and then select the value NO of the field USO, i will show only the possible IDSITIO that has NO for each values, 5 and 9.

For example the iDSITIO 14692, has a SI in USO, for NIVEL3 = 5 so i want that it’s not listed as possible when i select 5,9 and NO.

I understand why is behaving, because i understand the QV logic, but i need this trick.

I need a set analysis like this :

Concat(DISTINCT {$<

FlagCY={1},

M.FECHA={">=$(#desde)<=$(#hasta)"},

USO={'Si'},

NIVEL3= {'1'},

IDSITIO= p({$<FlagCY={1},USO ={'*'}-{'Si'},M.NIVEL2 ={'01'},NIVEL3={'*'}-{'1'},M.FECHA={">=$(#desde)<=$(#hasta)"}>} IDSITIO)>} IDSITIO,'","')

I need IDSITIOS that has SI on NIVEL3=1 and NO on every other value of NIVEL3, not in someone of them.

Thanks

Albert

Re: Selection logic / Set Analysis

swuehl — Fri, 10 Jul 2015 10:57:19 GMT

Maybe like attached?

Re: Selection logic / Set Analysis

Fri, 10 Jul 2015 11:55:31 GMT

Hi,

Thanks for your response. Seems to be what i'm looking for, but i cant understand the =count double sentence.

Could you adapt it to my set analysis expression or explain me what it does ?

=Concat(DISTINCT {$<

M.RESPUESTA ={'Si'},

M.MARCA = p(MARCALEVADURAF_T),

M.ID_SITIO_UNIFICADO = p({$<M.RESPUESTA ={'*'}-{'Si'},M.MARCA={'*'}-p(MARCALEVADURAF_T)>}M.ID_SITIO_UNIFICADO)>}

M.ID_SITIO_UNIFICADO,'","')

So many thanks

Albert

Re: Selection logic / Set Analysis

Gysbert_Wassenaar — Fri, 10 Jul 2015 12:22:48 GMT

Try: =concat({<NIVEL3={1}, USO={'Si'},IDSITIO=e({<NIVEL3-={1}, USO={'Si'}>}IDSITIO)>}IDSITIO,', ')

Re: Selection logic / Set Analysis

swuehl — Sat, 11 Jul 2015 11:10:56 GMT

This is my expression:

=Concat(DISTINCT {$<

USO={'Si'},

NIVEL3= {'1'},

IDSITIO= {"=count({$<USO ={'No'},NIVEL3 -= {'1'}>} NIVEL3)=count({1}distinct NIVEL3)-1"}

>} IDSITIO,',')

The 'double count' is an advanced search expression on field IDSITIO. Basically it's telling QV to only consider IDSITIO where the

first count() equals second count()-1.

The first count will count the NIVEL3 per IDSITIO with a filter on USO = No and Nivel not equal 1.

The second count will count DSITINCT NIVEL3 per IDSITION ignoring any selections.

Another solution could be

IDSITIO= {"=count({$<USO ={'No'},NIVEL3 -= {'1'}>} DISTINCT NIVEL3)=count({$<USO ={'No'},NIVEL3 -= {'1'}>} TOTAL DISTINCT NIVEL3)"} >}

The first count() was modified to only count DISTINCT NIVEL3, the second count() to count distinct NIVEL3 (excluding the ones with 'Yes' or NIVEL3 =1 across all IDSITIO.

The way you change the search expressions, you might get different results based on the data in your data model, for example when one IDSITIO has not a complete set of NIVEL3 records (complete in a sense that all possible values of NIVEL3 appear for every IDSITIO), or maybe multiple records per NIVEL3 with different USO values.

Also Gysbert's solution is working fine, but may differ from mine when the data set is different from the one you've posted.

Mine is also regarding user selections on field IDSITIO, while you would need to apply the intersection operator * to Gysbert's if you want to do the same:

=concat({<NIVEL3={1}, USO={'Si'},IDSITIO *= e({<NIVEL3-={1}, USO={'Si'}>}IDSITIO)>}IDSITIO,', ')

Hence there might be different solutions necessary based on the full requirements of your business. There is no way around getting an understanding of how each solution works.

And sorry, I think I can't apply my solution to your posted set expression, since I don't have a full picture of all the fields stated therein.

Re: Selection logic / Set Analysis

Mon, 20 Jul 2015 09:06:49 GMT

Thank you very much swuehl

I solved my case with your accurate explanation.

Regards,

Albert