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: 
vkish16161
Creator III
Creator III

P and E Syntax issue??

All,

Is there anything wrong with below image? why is P not highlighting in Blue?

The intent here is to only get count of claims where Opened date is the same as Reported date.

P and E.JPG

My other solution that works is Count (if([Claims Opened Date]=[Claims Reported Date],[Claims Number],null())) but not sure why P and E wouldn't work?

hicrwunderlichmarcus_sommermtostalwar1

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

The expression

   <[Claims Opened Date]=P([Claims Reported Date])>

will not do what you want: P() is a set, i.e. it contains multiple values. So the expression will be equivalent to making a selection of multiple values in [Claims Opened Date]. You instead want a comparison made value-by-value, where the two dates are compared.

Generally, set analysis cannot be used for a row-by-row comparison this way.

Further, the search

   count([Class Number]={"=[Claims Opened Date] = [Claims Reported Date]"}>[Claims Number])

will not always work either: Here, a hypercube with [Class Number] as dimension will be created in the background, and

   [Claims Opened Date] = [Claims Reported Date]

will be used as measure. Records with this evaluated as TRUE will be included in the search result. But classes with multiple open dates and multiple report dates will be evaluated as

   NULL = NULL

which in turn is FALSE, even if there are individual dates that match.  So, the search will not (always) work either.

The solution with the If() function will work fine, though.

HIC

View solution in original post

7 Replies
ali_hijazi
Partner - Master II
Partner - Master II

try:

count([Class Number]={"=[Claims Opened Date] = [Claims Reported Date]"}>[Claims Number])

your expression in the picture should work unless the 2 dates fields have different format

try converting both to numbers

I can walk on water when it freezes
vkish16161
Creator III
Creator III
Author

Ali,

The below will work, I tried this already :

count({<[Claims Number]={"=[Claims Opened Date] = [Claims Reported Date]"}>}[Claims Number])


BUT


is there a reason why "P" isn't highlighting in Blue?


and also, I believe, set analysis takes the numeric part of the field, so conversion shouldn't matter.


ali_hijazi
Partner - Master II
Partner - Master II

no idea why it is not being highlighted in Blue but it should work
last week I was working on expression to get the customers not served in a selected period of time and I used the E() and it works perfectly despite the fact that the E is not highlighted in blue

date(

max(

{1

<

salesman_name=$::salesman_name,

company_name=$::company_name,

branch_customer.customer_code = E(

{

<

trx_type={'Sales'}

,branch_customer.customer_status={'active'}

,branch_customer.customer_code = {"=Sum({<trx_type={'Sales'}>} amount_usd)>0"}

>

} branch_customer.customer_code

)

,trx_type={'sales'}

,branch_customer.customer_status={'active'}

,YEAR=,MONTH=,MONTH_NUM=,MONTH_YEAR=,DAY=

,trx_date={'<=$(vEOM_LastMonth)'}

>

}trx_date)

)

I can walk on water when it freezes
hic
Former Employee
Former Employee

The expression

   <[Claims Opened Date]=P([Claims Reported Date])>

will not do what you want: P() is a set, i.e. it contains multiple values. So the expression will be equivalent to making a selection of multiple values in [Claims Opened Date]. You instead want a comparison made value-by-value, where the two dates are compared.

Generally, set analysis cannot be used for a row-by-row comparison this way.

Further, the search

   count([Class Number]={"=[Claims Opened Date] = [Claims Reported Date]"}>[Claims Number])

will not always work either: Here, a hypercube with [Class Number] as dimension will be created in the background, and

   [Claims Opened Date] = [Claims Reported Date]

will be used as measure. Records with this evaluated as TRUE will be included in the search result. But classes with multiple open dates and multiple report dates will be evaluated as

   NULL = NULL

which in turn is FALSE, even if there are individual dates that match.  So, the search will not (always) work either.

The solution with the If() function will work fine, though.

HIC

vkish16161
Creator III
Creator III
Author

rwunderlich

According to Henric below may not work.

1.JPG

hic
Former Employee
Former Employee

The construction

   {<RecID={"=F1=F2"}>}

will work excellently if RecID has a grain that is fine enough, i.e. if there are no multiple values in F1 and F2 for any RecID. The name "RecID" implies that this is an ID per record - an ID on the lowest level. And then the expression will work.


In your case, you have

   {<Class={"=Date1=Date2"}>}

which is an expression with identical structure, and the same is of course true here: If Class has a grain fine enough, then it will work. But it will not work if a Class has multiple values of Date1 (or Date2) associated with it. And the name "Class Number" sounds as if this is a possibility.


HIC

vkish16161
Creator III
Creator III
Author

Thanks Henric for clarifying.

Also, Set Expression Builder was released in Qlik Cloud today but I believe there's a minor flaw in it.

Could you please clarify it?

Here's the link: Bug ?? in Set Expressions Builder in QS Nov 2018?

Thanks!

hic