topic Re: Extract values depending on periods condition in QlikView

Extract values depending on periods condition

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

in attached sample file I would create a new list box element containing those combinations of AGENTE and CLIENTE pairs that don't have Periods value in its PERIODO_0 field.

For instance, about first pair "1, 100076" I would like to see as a final result:

1 100076 2011-01

1 100076 2011-04

1 100076 2011-08

1 100076 2011-10

1 100076 2011-11

1 100076 2011-12

1 100076 2012-02

1 100076 2012-07

1 100076 2012-08

...

Thank you in advance and best regards.

Marco Pikiri

Re: Extract values depending on periods condition

swuehl — Thu, 05 May 2016 18:04:31 GMT

I think you first need to get the two fields format to be the same, currently PERIODO_0 is text only, PERIODO shows dual values.

I did this (not having your source files) with a partial reload:

AgentiClienti00:

ADD LOAD

ID_AZIENDA_0,

CLIENTE_0,

M_AGENTE_1_KEY,

Date#(PERIODO_0,'YYYY-MM') as PERIODO_0,

1 as Dummy

Resident AgentiClienti0;

DROP TABLE AgentiClienti0;

Now you can create a list box with a field expression like

=Aggr(Only({<Periodo -= p(PERIODO_0) >}Periodo),Periodo)

Re: Extract values depending on periods condition

Fri, 06 May 2016 19:50:26 GMT

Hi swuehl,

thank you very much for your support.

As conclusion of all I'd like now to show results on a grid element showing data like in my first post (1, 100076, 2011-01, ...).

I've spent all day, but didn't reach desired result... Only thing I have correct just when a single combination of AGENTE and CLIENTE is selected.

I'm now a bit confused too, I can't understand which could be the right object to use (pivot table? straight table?) or maybe I should also think about reanalyze data model?

Re: Extract values depending on periods condition

swuehl — Fri, 06 May 2016 20:23:04 GMT

Set analysis is evaluated once per chart, not per your dimension lines.

So if you expect the p() function to consider your current dimension values, this won't work.

But if you don't expect that, you can create a straight table with three dimensions, Agente, Cliente, Periodo and as hidden expression

=Only({<Periodo -= p(PERIODO_0) >}Periodo)

Re: Extract values depending on periods condition

Fri, 06 May 2016 21:55:43 GMT

Swuehl,

if you try now to unselect CLIENTE 100076 I see the behavior I was mentioning: Periodo values disappear, just the common values between two CLIENTEs remain available and this is not what I want.

In other words, unselecting 100076 then original lines should stay there and I expect that new lines for other CLIENTE show up.

Regards.

Marco

Re: Extract values depending on periods condition

Mon, 09 May 2016 09:08:11 GMT

Hello,

any idea about this issue?

Thanks.

Marco

Re: Extract values depending on periods condition

Fri, 20 May 2016 10:47:46 GMT

Hello,

I'm sorry if I insist again, but I would need to know if desired behaviour could be obtained or not.

Thank you and best regards.

Marco Pikiri

Re: Extract values depending on periods condition

swuehl — Fri, 20 May 2016 15:30:21 GMT

Maybe like attached

Re: Extract values depending on periods condition

Fri, 20 May 2016 15:58:16 GMT

Unbelievable. That's the final result I was expecting, thank you again.

But if you have other 5 mins of availability could you explain me in short your formula:

If(Sum( Aggr(Periodo = PERIODO_0, M_AGENTE_1_KEY, CLIENTE_0,Periodo, PERIODO_0))=0, 1,0) ?

Best regards.

Marco Pikiri

Re: Extract values depending on periods condition

swuehl — Fri, 20 May 2016 16:06:26 GMT

The advanced aggregation function is building a virtual table, using M_AGENTE_1_KEY, CLIENTE_0,Periodo, PERIODO_0 as dimensions and

=Periodo = PERIODO_0

as expression. Since Periodo, PERIODO_0 are in different tables, all combinations of possible field values are created (cartesian product).

The expression is a comparison of the dimensional values and returns -1 if there is a match and 0 if not.

Then I aggregate / sum this expression column projecting the virtual table to the outer chart dimensions.

If there is no PERIODO_0 for a chart dimension value Periodo, 1 is returned, else 0, which can be used to hide the chart lines we don't want to see.

Re: Extract values depending on periods condition

Fri, 20 May 2016 16:20:48 GMT

Ok, this helps me to understand that if I temporarily disable your expression and if I add another one like:

If(1=0,1,0)

then all rows always are hidden due to 0 value always returned. Before your explanation I was convinced that with this kind of expression all rows should continue to appear with always a value of 0 aside. In fact, if the expression modifies to the opposite:

If(1=0,0,1)

or:

If(1=1,1,0)

now all rows return visible because in this case 1 value always returned (i.e. a value different from '0'). Is this correct?

Best regards.

Marco Pikiri