topic Re: Condition on a field in QlikView

Condition on a field

Fri, 26 Sep 2014 07:33:20 GMT

Hello,

Here is my little problem.

I need to create a dashboard whith the different quotes made for a customer. Each quote has a specific and unique number but can have different variant noted v1, v2 etc.

Each variant has a status code from 0 to 4 (0 being in pending, 1 accepted, 2 discarded etc). When a customer accept one of the variant it's status change to 1 (since its accepted). However the other variant remain with the status from before (0 or 2 or 3 etc).

For my dashboard I would like to show the quotes that are still pending. There is no need to show the variant pending but only the quotes. So when a variant has been accepted, the quote cannot appear.

I tried using a condition on the field of the quote like if(STATUS <>'1', QUOTE_NUM) but it only shows quote that have one variant which its status is different from 1. The matter is that I have quotes with multiple variants and even if one variant has a status of '1', the quote is still appearing due to the fact that other variants have a status different from 1.

Is there another way of not showing these?

Thanks a lot!

Re: Condition on a field

ychaitanya — Fri, 26 Sep 2014 07:37:32 GMT

Can you share QCW and Sample Data and Condition?

Regards

Chaitanya

Re: Condition on a field

Fri, 26 Sep 2014 07:40:36 GMT

I'm still on the free version of qlikview, waiting for the license in a few days/weeks 😕 sorry

Re: Condition on a field

Anonymous — Fri, 26 Sep 2014 07:42:33 GMT

you can use if the Status contains like 013

if (purgechar(yourstatus,'0234')<>'1',QUOTE_NUM)

if there are Separators you Need to add the separator as well

if (purgechar(yourstatus,'0234;'<>'1', QUOTE_NUM)

Re: Condition on a field

Fri, 26 Sep 2014 08:09:49 GMT

Hi, it seems to work well, but in another table I wold like to show only the quotes that have been accepted. I transformed your formula into if (purgechar(yourstatus,'0234')='1',QUOTE_NUM) and my problem comes back. The only quotes that appears have only one variant that has been accepted. This formula seems to reject all the quotes with multiple variants 😕

Re: Condition on a field

Anonymous — Fri, 26 Sep 2014 08:13:30 GMT

can you show example when it Fails?

the purgechar eliminates all given characters (and it works with the <> '1')

a Status of '01234' will Keep '1' after purgechar

Re: Condition on a field

Fri, 26 Sep 2014 08:52:33 GMT

So on more thant 800 quotes, only 4 quotes appears to be accepted which is not plausible. When I looked at the different quotes they only have one variant (which is the accepted one by the way). Therefore using this test, it rejects all the other quotes that have a variant equals to 0, 2, 3 or 4 😕

Re: Condition on a field

Anonymous — Fri, 26 Sep 2014 09:02:11 GMT

can you append an example?

or try to figure it out by yourself by craeting new TextBox

with the Definition "=purgechar('1','0234') where you define the Status where it does not work in the

first Parameter. how does JD_STATUS_DE_SUIVI Looks like in the cases where

purgechar doesnot work?

Re: Condition on a field

jonathandienst — Fri, 26 Sep 2014 09:18:33 GMT

First of all - the fact that you are on personal edition is no obstacle to uploading your model or a sample thereof. Click Use advanced editor and upload the qvw. Any users with licenses can then open your document.

Secondly, during your load, you need to create a flag against the quote number (not the version number). Something like this after loading the version data:

AcceptFlags:

LOAD QuoteNo,

If(WildMatch(Concat(Status), '*1*'), 1, 0) As AcceptedFlag

Resident Data

Group By QuoteNo;

(change this to match your field names and main data table name - this is why its simpler if you upload a sample)

Now you can use AcceptedFlag in a set expression:

Count({<AcceptedFlag = {'0'}>} .....)

HTH

Jonathan

Re: Re: Condition on a field

Fri, 26 Sep 2014 09:35:20 GMT

Your idea is great, I was just thinking about concatenating the different versions together

Here is my file and the datas for the quotes, just look in the first tab, the other are not running yet.

Thanks!