18 Replies Latest reply: Jul 6, 2015 10:48 AM by Willy GARNIER

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

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

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

Maybe like attached?

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

Hello,

I have changed the first column with:

min(If(Serie_economique=1, Palier)) instead of min(Palier)) , beacause I want the minimum with Serie_economique=1 --> It works (but I did the same thing with my filter)

And named it "Palier1"

For the second column, I tried this:

=if(

count(Distinct Palier)>1

, Min(

If(Serie_economique=0 and Palier>[Palier1]

, Palier

)

)

)

But it is not working

Can I use an expression in another one ?

And if YES, is it not working because of the Min function ?

Regards,

Willy

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

What is [Palier1]? A reference to another expression (the one you mentioned first)? That won't work.

But I don't think you need this second AND condition, try

=if(

count(Distinct Palier)>1

, Min(

If(Serie_economique=0

, Palier

)

)

)

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

You can simplify your requested expressions by using the three dimensions and two expressions as shown here:

Code_Article Code_Depot Num_Contrat min({<Serie_economique = {1} >}Palier) min({<Serie_economique = {0} >}Palier)
11112
12110
123100
21210002000
44444443000
• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

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

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

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.

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

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

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

Yes [Palier1] is the first expression: min(If(Serie_economique=1, Palier))

I can not use it in another expression ?

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

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

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

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

)

)

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

In test can u try this:

=aggr(Code_Article,Code_Depot, Num_Contrat,Palier)

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

The aggr give me the number of lines corresponding to the combination of Code_article , Code_depot , Num_Contrat

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

Hi Willy,

see attachment.

Regards,

Antonio

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

Hello Antonio,

Palier column is OK, you gave me another solution to find it.

But my "Test" column is empty in your qvw And this is the one I need.

Thank you, Willy

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

Hi Willy,

why column Test is empty ?

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

Hi Antonio,

I have this when I open you QVW:

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

????!!!!

I try to attach again

• ###### Re: Need help with Set Analysis and test like "if exists" (QVW in)

Same thing