topic Expression syntax in QlikView

Expression syntax

Mon, 05 Oct 2015 17:49:02 GMT

Hi,

I want to get the count of ids where the difference between two dates is greater than 30. Please help me with the syntax.

Thanks,

Sudha.

Re: Expression syntax

Gysbert_Wassenaar — Mon, 05 Oct 2015 17:52:27 GMT

Try something like this: count({<UPDATE_DT={"<$(=Today()-30)"}>} distinct CASE_ID)

If that doesn't work please post a small qlikview document that demonstrates the problem so we can find out what will work correctly with your data.

Re: Expression syntax

petter — Mon, 05 Oct 2015 17:52:40 GMT

Count( { <CASE_ID={"=Now()-Update_DT>30"}> } CASE_ID )

maybe even with DISTINCT

Count( DISTINCT { <CASE_ID={"=Now()-Update_DT>30"}> } CASE_ID )

Re: Expression syntax

markgraham123 — Mon, 05 Oct 2015 17:59:36 GMT

Gysbert and Petter were right.

other way is using the If condition,

=Count(If((Date(Now())-Date(UPDATE_DT)) > 30, CASE_ID)

or Distinct:

=Count(Distinct(If((Date(Now())-Date(UPDATE_DT)) > 30, CASE_ID))

Re: Expression syntax

petter — Mon, 05 Oct 2015 18:07:31 GMT

The solution that Gysbert and I recommend is using a so-called Set Expression.

The set which is enclosed in curly braces { and } defines which subset of rows that should be used in the Count.

Furthermore the angle brackets < and > enclosed a set modifier that works as a where. So it says that the field mentioned CASE_ID (in my suggestion) should be based on the search condition which is enclosed in double quotation marks and starts with an equal sign. The search is then for rows where the difference between now and Update_DT is greater than 30.

Re: Expression syntax

Mon, 05 Oct 2015 18:15:40 GMT

Thanks for the help and the great explanation on the syntax.

Could you please check what I am missing -

None of the above syntax is producing the result. I don't see any error in the editor but it just outputs 0 everywhere.

Thanks,

Sudha.

Re: Expression syntax

maxgro — Mon, 05 Oct 2015 19:06:47 GMT

maybe

count({<UPDATE_DT={"<$(=Date(Today()-30))"}>} distinct CASE_ID)

see attachment

Re: Expression syntax

Mon, 05 Oct 2015 19:26:42 GMT

Hi,

Please let me know how to attach a qvw file. The attachment option I see here accepts only urls. Is there a way to choose a file from my local and attach it here?

Thanks,

Sudha.

Re: Expression syntax

maxgro — Mon, 05 Oct 2015 19:29:46 GMT

use advanced editor (top right)

attach (bottom right)

Re: Expression syntax

Mon, 05 Oct 2015 19:51:10 GMT

Thank you!

I have the QVW here. Please have a look. It's only displaying zeros.

Re: Expression syntax

petter — Mon, 05 Oct 2015 20:29:05 GMT

I think this works for you:

Re: Expression syntax

Mon, 05 Oct 2015 20:30:40 GMT

Thank you so much!!

Re: Expression syntax

petter — Mon, 05 Oct 2015 20:31:36 GMT

You're welcome

Re: Expression syntax

maxgro — Mon, 05 Oct 2015 20:35:43 GMT

you have a timestamp, not a date, try with

count({<UPDATE_DT={"<$(=timestamp(Today()-30))"}>} distinct CASE_ID)

or you can remove the time part from your timestamp in the load script, using

date(floor(UPDATE_DT)) in the load part of the script

LOAD

CASE_ID,

....

date(floor(UPDATE_DT)) as UPDATE_DT,

...

NM_FULL

;

SQL SELECT

BEACON.VW_CASES.CASE_ID,

BEACON.VW_CASES.STATUS,

BEACON.VW_CASES.TYPE,

BEACON.VW_CASES.CREATED_BY_NM,

BEACON.VW_CASES.CREATE_DT,

BEACON.VW_CASES.ASSIGNED_TO_NM,

BEACON.VW_CASES.ACCOUNT_CD,

BEACON.VW_CASES.ACCOUNT_LONG_NM,

ASSIGNED_TO_HIREARCHY.ID_STD_MGR_DIRECT_NM,

ASSIGNED_TO_HIREARCHY.ID_STD_MGR_LV4_NM,

BEACON.VW_CASES.UPDATE_DT,

UPDATED_BY.USER_NAME,

BEACON.VW_CASES.ASSIGNED_TO,

ASSIGNED_TO_HIREARCHY.NM_FULL

FROM

BEACON.VW_CASES,

BEACON.VW_WORKFORCE_JPMC_V3 ASSIGNED_TO_HIREARCHY,

BEACON.VW_USERS UPDATED_BY

WHERE

( BEACON.VW_CASES.UPDATE_BY_SID=UPDATED_BY.USER_SID(+) )

AND ( ASSIGNED_TO_HIREARCHY.ID_STANDARD(+)=BEACON.VW_CASES.ASSIGNED_TO )

AND

(

BEACON.VW_CASES.STATUS NOT IN ( 'Ops Resolved By Me','Resolved-Rejected-OpsTeam','Complete','Resolved-Completed','Resolved-Deleted','Complete-Passed','Complete-Failed','Deleted','Closed' )

AND

(

ASSIGNED_TO_HIREARCHY.ID_STD_MGR_LVL4 IN ( 'N054267' )

BEACON.VW_CASES.ASSIGNED_TO IN ( 'N054267' )

ASSIGNED_TO_HIREARCHY.ID_STD_MGR_LVL5 IN ( 'N054267' )

)

);

and come back to the previous expression

count({<UPDATE_DT={"<$(=Date(Today()-30))"}>} distinct CASE_ID)