Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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_ArticleCode_DepotNum_ContratPalierSerie_economique
11111
11120
121101
1231001
21210001
21220000
44430000
44444441
44455550
4446666

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_ArticleCode_DepotNum_ContratFiltrePalier
111OK1
121OK10
123OK100
212OK1000
444OK4444

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_ArticleCode_DepotNum_ContratFiltrePalierTest
111OK12
121OK10
123OK100
212OK10002000
444OK44445555

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

18 Replies
swuehl
MVP
MVP

Maybe like attached?

Not applicable
Author

In test can u try this:

=aggr(Code_Article,Code_Depot, Num_Contrat,Palier)

antoniotiman
Master III
Master III

Hi Willy,

see attachment.

Regards,

Antonio

Not applicable
Author

Thank you for the answer.

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

Not applicable
Author

Hello,

thank you for the answer.

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

swuehl
MVP
MVP

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

  )

  )

  )

swuehl
MVP
MVP

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
Not applicable
Author

Hello Antonio,

thank you for your proposal.

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

Not applicable
Author

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

I can not use it in another expression ?