Need help with Set Analysis and test like "if exists" (QVW in)

[#1]

Hello,

First I have joined a QVW to show you what I want to do.

I have several lines with:

- Code_article = Item number

- Code_depot = depository

- Num_Contrat = contract number

Others are less important for the understanding.

Code_Article	Code_Depot	Num_Contrat	Palier	Serie_economique
1	1	1	1	1
1	1	1	2	0
1	2	1	10	1
1	2	3	100	1
2	1	2	1000	1
2	1	2	2000	0
4	4	4	3000	0
4	4	4	4444	1
4	4	4	5555	0
4	4	4	6666	0

For each combination of Code_article , Code_depot , Num_Contrat  I have one line with "Serie_economique" = 1.

I want one line for each so:

Code_Article	Code_Depot	Num_Contrat	Filtre	Palier
1	1	1	OK	1
1	2	1	OK	10
1	2	3	OK	100
2	1	2	OK	1000
4	4	4	OK	4444

I have made a filter to show only these lines.

It is OK, I have these lines

But I want more

I would like to know if another line exists with "Serie_economique" = 0 and more, the min value with "Serie_economique" = 0 which is superior to the one with "Serie_economique" = 1 ...

Something like that:

Code_Article	Code_Depot	Num_Contrat	Filtre	Palier	Test
1	1	1	OK	1	2
1	2	1	OK	10
1	2	3	OK	100
2	1	2	OK	1000	2000
4	4	4	OK	4444	5555

I need help for column "Test" in this table.

You can see my tests in the QVW but I can not find the good expression. I only tried to find teh max value for the combination of Code_article , Code_depot , Num_Contrat before doing more complex case.

But I always have:

- the same value as "Palier"

- the max value of all the table

I can not do it in my script because my real need is much more complicated

With my example, I want to understand how it is working in a "simple" qvw.

Help me please to understand why and how to solve my problem.

Thank you,

Willy

[#2]

Yep but it is not the result I need

I want the min value of Palier as you did, BUT which is superior to the line with Serie_economique = 1

[swuehl]

In general yes, but not if you reference an expression that is an aggregation function (min() ) from within another aggregation function.

[swuehl · Accepted solution]

Oh, yes, I've missed that requirement, sorry.

You can create a flag in your script, that indicates the required Palier value, or use a bit complicated expression in your chart.

Both methods shown in attached sample file.

[#5]

Thank you  swuehl .

I tried without min in the first expression, but it is not working

Palier1=Palier

And

Test:

=min(

  If(

  Serie_economique=0

  And Palier >= Palier1

  , Palier

  )

)

[antoniotiman]

Hi Willy,

why column Test is empty ?

[#7]

Thank you for all your answers

I can not do it in my script because may real need is much more complicated.

Your expression seems to work, I will try it in my real example.

Regards,

Willy

[#8]

Hi Antonio,

I have this when I open you QVW:

[antoniotiman]

????!!!!

I try to attach again

[#10]

Same thing

But swuehl answered m.

Thank you for your time.

Willy