topic Dollar-Sign Expansion's Arcane Syntax... in QlikView

Dollar-Sign Expansion's Arcane Syntax...

Tue, 22 Sep 2015 13:08:23 GMT

... continues to give me fits.

I'm new at this. Be gentle.

Here's the problem.

This expression, to count the number of employees who meet a certain criteria, seems to work just fine:

=Num(Count({<ALL_EFFECTIVE_START_DATE={"<=$(=vAsOfDate)"},
              ALL_EFFECTIVE_END_DATE={">$(=vAsOfDate)"},
              ALL_ASG_START_DATE={"<=$(=vAsOfDate)"},
              ALL_ASG_END_DATE={">$(=vAsOfDate)"},
              ALL_JOB_TITLE={"$(=IF($(vOnlyTeachers)=1,'TEACHER','*'))"}>}
ALL_EMPLOYEE_NUMBER),'#,##0')

HOWEVER...

When I try to insert this line, containing a variable to exclude certain other types (after the END_DATE line above), it incorrectly returns zero:

ALL_USER_PERSON_TYPE={"-=$(=vUserStatusExceptions)"},

vUserStatusExceptions is defined thusly:

SET vUserStatusExceptions = 'SEPARATED' 'INACTIVE' 'AC TIVE APPLICATION' 'ACCEPTED' 'ACTIVE ASSIGNMENT' 'NEW HIRE';

So what is it this time that I'm doing wrong?

Thanks in advance.

Re: Dollar-Sign Expansion's Arcane Syntax...

pokassov — Tue, 22 Sep 2015 13:15:24 GMT

Hi!

Try this

ALL_USER_PERSON_TYPE-={$(vUserStatusExceptions)}

SET vUserStatusExceptions = 'SEPARATED', 'INACTIVE','AC TIVE APPLICATION','ACCEPTED','ACTIVE ASSIGNMENT','NEW HIRE';

Re: Dollar-Sign Expansion's Arcane Syntax...

sinanozdemir — Tue, 22 Sep 2015 13:16:25 GMT

Hi,

Can you try this?

ALL_USER_PERSON_TYPE -={"$(vUserStatusExceptions)"}

Thanks

Re: Dollar-Sign Expansion's Arcane Syntax...

Tue, 22 Sep 2015 13:50:38 GMT

Thanks. Unfortunately, that returned an ERROR

Re: Dollar-Sign Expansion's Arcane Syntax...

jonathandienst — Tue, 22 Sep 2015 13:53:09 GMT

Sergey's expression looks OK to me. Did you confirm that vUserStatusExceptions contains what you expect?

Re: Dollar-Sign Expansion's Arcane Syntax...

sasiparupudi1 — Tue, 22 Sep 2015 14:00:30 GMT

Please try

SET vUserStatusExceptions = 'SEPARATED', 'INACTIVE','AC TIVE APPLICATION','ACCEPTED','ACTIVE ASSIGNMENT','NEW HIRE';

=Num(Count({<ALL_EFFECTIVE_START_DATE={"<=$(=vAsOfDate)"},

ALL_EFFECTIVE_END_DATE={">$(=vAsOfDate)"},

ALL_ASG_START_DATE={"<=$(=vAsOfDate)"},

ALL_ASG_END_DATE={">$(=vAsOfDate)"},

ALL_USER_PERSON_TYPE-={"$(=vUserStatusExceptions)"},

ALL_JOB_TITLE={"$(=IF($(vOnlyTeachers)=1,'TEACHER','*'))"}>}

ALL_EMPLOYEE_NUMBER),'#,##0')

hth

Sasi

Re: Dollar-Sign Expansion's Arcane Syntax...

sasiparupudi1 — Tue, 22 Sep 2015 14:02:36 GMT

Please note that the code editor might show error line because on the - sign but the expression should be ok

Re: Dollar-Sign Expansion's Arcane Syntax...

Tue, 22 Sep 2015 14:02:45 GMT

Thanks. This answer also refused to work correctly. The results didn't change at all from when the line was excluded altogether.

Re: Dollar-Sign Expansion's Arcane Syntax...

Tue, 22 Sep 2015 17:49:28 GMT

Thanks.

Unfortunately, this seems to have no effect at all, like QV is ignoring the line altogether.

The editor also looks like it has a problem with the -=. It puts a little mark underneath it as if to say, "I don't know what this is"

Re: Dollar-Sign Expansion's Arcane Syntax...

sunny_talwar — Tue, 22 Sep 2015 18:06:14 GMT

What happen when you actually add the value instead of using the variable?

=Num(Count({<ALL_EFFECTIVE_START_DATE={"<=$(=vAsOfDate)"},

ALL_EFFECTIVE_END_DATE={">$(=vAsOfDate)"},

ALL_ASG_START_DATE={"<=$(=vAsOfDate)"},

ALL_ASG_END_DATE={">$(=vAsOfDate)"},

ALL_USER_PERSON_TYPE-={'SEPARATED' 'INACTIVE' 'AC TIVE APPLICATION' 'ACCEPTED' 'ACTIVE ASSIGNMENT' 'NEW HIRE'},ALL_JOB_TITLE {"$(=IF($(vOnlyTeachers)=1,'TEACHER','*'))"}>} ALL_EMPLOYEE_NUMBER),'#,##0')

Re: Dollar-Sign Expansion's Arcane Syntax...

sunny_talwar — Tue, 22 Sep 2015 18:07:51 GMT

If the above works, can you try one of these:

=Num(Count({<ALL_EFFECTIVE_START_DATE={"<=$(=vAsOfDate)"},

ALL_EFFECTIVE_END_DATE={">$(=vAsOfDate)"},

ALL_ASG_START_DATE={"<=$(=vAsOfDate)"},

ALL_ASG_END_DATE={">$(=vAsOfDate)"},

ALL_USER_PERSON_TYPE-={$(vUserStatusExceptions)},ALL_JOB_TITLE {"$(=IF($(vOnlyTeachers)=1,'TEACHER','*'))"}>} ALL_EMPLOYEE_NUMBER),'#,##0')

=Num(Count({<ALL_EFFECTIVE_START_DATE={"<=$(=vAsOfDate)"},

ALL_EFFECTIVE_END_DATE={">$(=vAsOfDate)"},

ALL_ASG_START_DATE={"<=$(=vAsOfDate)"},

ALL_ASG_END_DATE={">$(=vAsOfDate)"},

ALL_USER_PERSON_TYPE-={$(=vUserStatusExceptions)},ALL_JOB_TITLE {"$(=IF($(vOnlyTeachers)=1,'TEACHER','*'))"}>} ALL_EMPLOYEE_NUMBER),'#,##0')

Re: Dollar-Sign Expansion's Arcane Syntax...

Tue, 22 Sep 2015 18:18:03 GMT

Thanks.

Unfortunately, this seems to have no effect at all, just like the previous suggestion.

Also like the previous suggestion, the editor looks like it has a problem with the -=. It puts a little squiggly mark underneath it as if to say, "You can't do that here"

Re: Dollar-Sign Expansion's Arcane Syntax...

sunny_talwar — Tue, 22 Sep 2015 18:20:45 GMT

Even though you get that squiggle line underneath -=, it doesn't mean that your expression is incorrect. Still if you want to avoid it, try this:

=Num(Count({<ALL_EFFECTIVE_START_DATE={"<=$(=vAsOfDate)"},

ALL_EFFECTIVE_END_DATE={">$(=vAsOfDate)"},

ALL_ASG_START_DATE={"<=$(=vAsOfDate)"},

ALL_ASG_END_DATE={">$(=vAsOfDate)"},

ALL_USER_PERSON_TYPE = e({<ALL_USER_PERSON_TYPE = {'SEPARATED' 'INACTIVE' 'AC TIVE APPLICATION' 'ACCEPTED' 'ACTIVE ASSIGNMENT' 'NEW HIRE'}>}) ,ALL_JOB_TITLE {"$(=IF($(vOnlyTeachers)=1,'TEACHER','*'))"}>} ALL_EMPLOYEE_NUMBER),'#,##0')

Re: Dollar-Sign Expansion's Arcane Syntax...

sasiparupudi1 — Tue, 22 Sep 2015 18:22:22 GMT

There is a comma missing between the values in the exclusion list

=Num(Count({<ALL_EFFECTIVE_START_DATE={"<=$(=vAsOfDate)"},

ALL_EFFECTIVE_END_DATE={">$(=vAsOfDate)"},

ALL_ASG_START_DATE={"<=$(=vAsOfDate)"},

ALL_ASG_END_DATE={">$(=vAsOfDate)"},

ALL_USER_PERSON_TYPE-={'SEPARATED','INACTIVE','AC TIVE APPLICATION','ACCEPTED','ACTIVE ASSIGNMENT','NEW HIRE'},ALL_JOB_TITLE {"$(=IF($(vOnlyTeachers)=1,'TEACHER','*'))"}>} ALL_EMPLOYEE_NUMBER),'#,##0')

Re: Dollar-Sign Expansion's Arcane Syntax...

sunny_talwar — Tue, 22 Sep 2015 18:24:36 GMT

Good catch

=Num(Count({<ALL_EFFECTIVE_START_DATE={"<=$(=vAsOfDate)"},

ALL_EFFECTIVE_END_DATE={">$(=vAsOfDate)"},

ALL_ASG_START_DATE={"<=$(=vAsOfDate)"},

ALL_ASG_END_DATE={">$(=vAsOfDate)"},

ALL_USER_PERSON_TYPE = e({<ALL_USER_PERSON_TYPE ={'SEPARATED', 'INACTIVE', 'AC TIVE APPLICATION', 'ACCEPTED', 'ACTIVE ASSIGNMENT', 'NEW HIRE'}>}) ,ALL_JOB_TITLE {"$(=IF($(vOnlyTeachers)=1,'TEACHER','*'))"}>} ALL_EMPLOYEE_NUMBER),'#,##0')

Re: Dollar-Sign Expansion's Arcane Syntax...

Tue, 22 Sep 2015 18:28:40 GMT

Is that little "e" supposed to be there?

ALL_USER_PERSON_TYPE = e({<ALL_USER_PERSON_TYPE...

I'm guessing that's something I haven't seen yet

Re: Dollar-Sign Expansion's Arcane Syntax...

sunny_talwar — Tue, 22 Sep 2015 18:30:45 GMT

Yup, that little e means to exclude those values .

Re: Dollar-Sign Expansion's Arcane Syntax...

Tue, 22 Sep 2015 19:26:38 GMT

UPDATE:

OK, first of all, I have to apologize to everyone who was kind enough to take the time to respond.

For two reasons:

My boss was the one who was having this problem, and he asked me to post the question because he doesn't have an account here. So minor apologies for the fact that it wasn't my problem to begin with. I know that's not a big deal, though.

But I point that out because I don't want to look like an idiot, which, as you'll soon see, is a legitimate concern.

What IS, however, important, is that at least one of you almost certainly answered correctly, and at this point I'm not sure who.

I went in to my boss's office after the last suggestion and told him to copy and paste the answer. As he was doing so, I noticed a discrepancy between the title of the relevant table and the line in the expression he has been having so much trouble with all day.

It turns out he had been asking the wrong data field all along. As soon as I pointed it out and he changed it to the correct one, everything fell correctly into place. It shouldn't have been ALL_USER_PERSON_TYPE to begin with. That field will never contain any of those values.

So my apology stems from the fact that I don't know who to credit for the correct answer.

If you have any suggestions, please let me know.

Thanks for everyone's time.

Re: Dollar-Sign Expansion's Arcane Syntax...

sunny_talwar — Tue, 22 Sep 2015 19:30:45 GMT

Hahahaha

I guess you can randomly choose one answer as correct and reward others by choosing there responses as Helpful answers .

Best,

Sunny