topic Re: Expressions within Set Analysis in QlikView

Expressions within Set Analysis

quilldew — Fri, 13 Oct 2017 11:27:37 GMT

Hi,

I have an issue where I am trying to count NULL's within the EndDate field. I've tried various ways to count them eg, Len( Trim( Field ) ) = 0, and have decided the easiest for this application was just to create a NullFlag .

I am basically trying to include an 'OR' operator between two different fields, and then include other criteria I need to be met within the count.

The following is an example of what works:

count({<StartDate={"<=01/07/2016"}

,EndDate={">=01/07/2017"}

,Diag1={"10*","11*"}

,OtherStartDate={"<=01/07/2016"}

>}[KEY Client])

count({<StartDate={"<=01/07/2016"}

,Diag1={"10*","11*}

,OtherStartDate={"<=01/07/2016"}

,NullFlag={'1'}

>}[KEY Client])

Is there a way to condense the above script to something like (EndDate={">=01/07/2017"} + NullFlag={'1'})? as it becomes very long when Diag? = n

I've tried the above and it doesn't work

Also if you have any suggestions as to how I could use Len( Trim( Field ) ) = 0 instead of creating the flag (so I can use it on apps I don't have script access for) that would be great.

The problem I have is that I can't get Len( Trim( Field ) ) = 0 to evaluate properly when EndDate has been created by using 'date(floor())' on a timestamp field.

Re: Expressions within Set Analysis

Anil_Babu_Samineni — Fri, 13 Oct 2017 11:35:55 GMT

May be this?

count({<StartDate={"<=01/07/2016"}

, EndDate={">=01/07/2017"}

,Diag1={"10*","11*"}

,OtherStartDate={"<=01/07/2016"}> + <NullFlag={'1'}>

>}[KEY Client])

count({<StartDate={"<=01/07/2016"}

,Diag1={"10*","11*}

,OtherStartDate={"<=01/07/2016"}

,NullFlag={'1'}

>}[KEY Client])

Re: Expressions within Set Analysis

quilldew — Fri, 13 Oct 2017 11:41:13 GMT

Give me an error in expression unfortunately. It was one of the first things I thought of

Re: Expressions within Set Analysis

quilldew — Fri, 13 Oct 2017 11:43:21 GMT

Actually I put it as

EndDate={">=01/07/2017"}> + <NullFlag={'1'}>

But neither work.

Re: Expressions within Set Analysis

sunny_talwar — Fri, 13 Oct 2017 11:46:17 GMT

May be this

Count({<StartDate={"<=01/07/2016"}, Diag1={"10*","11*"}, OtherStartDate={"<=01/07/2016"}, [KEY Client] = {"=Only({1} EndDate) >= '01/07/2017' or Len(Trim(EndDate)) = 0"}>} [KEY Client])

Re: Expressions within Set Analysis

sunny_talwar — Fri, 13 Oct 2017 11:47:36 GMT

Or this

Count(

{<StartDate={"<=01/07/2016"}, EndDate={">=01/07/2017"}, Diag1={"10*","11*"}, OtherStartDate={"<=01/07/2016"}>

+<StartDate={"<=01/07/2016"}, Diag1={"10*","11*}, OtherStartDate={"<=01/07/2016"}, NullFlag={'1'}>}

[KEY Client])

Re: Expressions within Set Analysis

Anil_Babu_Samineni — Fri, 13 Oct 2017 11:48:36 GMT

I believe 1st one should work, You are Rock JI

Re: Expressions within Set Analysis

quilldew — Fri, 13 Oct 2017 13:12:18 GMT

This one works Sunny, and is a slight improvement on mine, but is still of a comparable length

Re: Expressions within Set Analysis

quilldew — Fri, 13 Oct 2017 13:14:57 GMT

Count({<StartDate={"<=01/07/2016"}, Diag1={"10*","11*"}, OtherStartDate={"<=01/07/2016"}, [KEY Client] = {"=Only({1} EndDate) >= '01/07/2017' or Len(Trim(EndDate)) = 0"}>} [KEY Client])

This is the kind of thing I am looking for, significantly shorter than my code, but it unfortunately doesn't work for me.

It is returning lots of records where the EndDate is before 01/07/2017.

Re: Expressions within Set Analysis

sunny_talwar — Fri, 13 Oct 2017 13:19:21 GMT

Do you have more than one EndDate for KEY Client field? If you do, are you looking for Min() or Max() End Date?

Re: Expressions within Set Analysis

quilldew — Fri, 13 Oct 2017 13:30:27 GMT

Only one EndDate per [KEY Client], and I'm not looking for either the min() or max() in any of the script.

Could it be down to the fact that EndDate is created from date(floor(EndDateTime)) ?

Re: Expressions within Set Analysis

sunny_talwar — Fri, 13 Oct 2017 13:35:58 GMT

Check this

Count({<StartDate={"<=01/07/2016"}, Diag1={"10*","11*"}, OtherStartDate={"<=01/07/2016"}, [KEY Client] = {"=Only({1} EndDate) >= MakeDate(2017, 1, 7) or Len(Trim(Only({1} EndDate))) = 0"}>} [KEY Client])

Was not sure if 01/07/2017 was Jan 7th or July 1st... I am assuming it was Jan 7th, but change the values in Make date to MakeDate(2017, 7, 1) if it was July 1st

Re: Expressions within Set Analysis

quilldew — Fri, 13 Oct 2017 13:51:18 GMT

It's 1st of July I'm looking at.

So I tried MakeDate(2017,7,1) and it is unfortunately still showing me lots of dates before that date.

I've just created a couple of listboxes, one for StartDate and one for EndDate.

I've noticed that all the entries for EndDate are right aligned, whereas for StartDate they are left aligned. Shouldn't they be the same? When I display them as timestamp they both have the time as 00:00 so the floor() has worked in the script at least.

Also every time I've used 'Len( Trim( Field ) ) = 0' on StartDate it gives me the expected results, but not on EndDate.

Re: Expressions within Set Analysis

sunny_talwar — Fri, 13 Oct 2017 13:54:32 GMT

Can you create a straight table with the following and share an image?

Dimension

[KEY Client]

Expressions

=Only({1} EndDate)

=Only({1} EndDate) >= MakeDate(2017, 7, 1)

=Len(Trim(Only({1} EndDate))) = 0

Re: Expressions within Set Analysis

quilldew — Fri, 13 Oct 2017 14:14:15 GMT

I can't post an image unfortunately as I've had to change the actual fieldnames we use (work are funny about making them public.

But I can tell you that for:

1) The EndDate was shown for each [KEY Client]

2) Shows -1 for any EndDates >= 1st July, and 0 for everything else including when EndDate is empty

3) Shows 0 for EndDate if it exists and -1 for empty.

So it looks like it's working properly. Just not within the set analysis.

Re: Expressions within Set Analysis

sunny_talwar — Fri, 13 Oct 2017 14:18:18 GMT

This is what I would have expected... which is good.... My guess is that this is working

Count([KEY Client] = {"=Only({1} EndDate) >= MakeDate(2017, 1, 7) or Len(Trim(Only({1} EndDate))) = 0"}>} [KEY Client])

But something else isn't working

Try adding these two expressions in the above chart that you created

1) Count([KEY Client] = {"=Only({1} EndDate) >= MakeDate(2017, 1, 7) or Len(Trim(Only({1} EndDate))) = 0"}>} [KEY Client])

2) Count({<StartDate={"<=01/07/2016"}, Diag1={"10*","11*"}, OtherStartDate={"<=01/07/2016"}, [KEY Client] = {"=Only({1} EndDate) >= MakeDate(2017, 1, 7) or Len(Trim(Only({1} EndDate))) = 0"}>} [KEY Client])

and tell me what you see.... (remember that the dimension is still KEY Client)

Re: Expressions within Set Analysis

quilldew — Fri, 13 Oct 2017 14:33:55 GMT

I swapped the makedate part to (2017,7,1) as i'm looking for July. I did try it both ways though.

Okay, so it looks like there is more than 1 EndDate per [KEY Client] as 2) is giving counts of 3 and 2 and 1, but they seem to be completely random apart from the fact they ONLY occur when the EndDate is empty. But there are plenty of records with empty EndDate's that have 0, and EVERY record with an EndDate is 0.

For 1) everything is empty (as in -). Which i'm assuming it shouldn't be.

Re: Expressions within Set Analysis

quilldew — Fri, 13 Oct 2017 14:37:37 GMT

Actually 1) has an error in expression

Re: Expressions within Set Analysis

sunny_talwar — Fri, 13 Oct 2017 14:37:56 GMT

My bad, missing {<

Count({<[KEY Client] = {"=Only({1} EndDate) >= MakeDate(2017, 1, 7) or Len(Trim(Only({1} EndDate))) = 0"}>} [KEY Client])

Re: Expressions within Set Analysis

quilldew — Fri, 13 Oct 2017 14:42:56 GMT

Yeah I spotted it just after I posted lol.

Now 1) is showing 1 for all EndDate's >= 1st July or empty. Which seems correct.