Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

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.

18 Replies
MVP
MVP

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

Maybe like attached?

Not applicable

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)

antoniotiman
Honored Contributor III

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

Hi Willy,

see attachment.

Regards,

Antonio

Not applicable

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

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

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

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

MVP
MVP

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

  )

  )

  )

MVP
MVP

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

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

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

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 ?

Community Browser