Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
Partner
Partner

Help With Set Expression - Syntax Error (and Possible Logic Error)

Hello:

 

I need to do a distinct count of Template Order ID when LOB Template Order does not contain a dash.  Sample data is attached.  Template Order ID is a calculated dimension using =if(index([LOB Template Order],'-')=0,[LOB Template Order]).  This filters out any Template Order ID's that have a dash.  In the attached sample data, I should wind up with 644 distinct Template Order ID's.  The expression I'm trying to use is =Count(if(index([LOB Template Order],'-')=0, [Template Order ID])).  Template Order ID is being flagged as a bad field name which I'm guessing is because it's a calculated dimension and not a real field that's loaded.  I think I'm close, but I'm obviously missing something and something probably simple.

 

As always, any and all help is appreciated.  Thanks in advance.

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Help With Set Expression - Syntax Error (and Possible Logic Error)

Like one of theses

Capture.PNG

View solution in original post

27 Replies
Highlighted

Re: Help With Set Expression - Syntax Error (and Possible Logic Error)

May be this

Count(DISTINCT {<[LOB Template Order] -= {"*-*"}>}[Template Order ID])

Highlighted

Re: Help With Set Expression - Syntax Error (and Possible Logic Error)

Or this

=Count(DISTINCT If(Index([LOB Template Order], '-') = 0, [Template Order ID]))

Capture.PNG

Highlighted
Partner
Partner

Re: Help With Set Expression - Syntax Error (and Possible Logic Error)

I get just zeros with this expression.  Trying the other.

Highlighted

Re: Help With Set Expression - Syntax Error (and Possible Logic Error)

I have attached a sample with the image showing that both the expressions give 644 as output.... isn't that what you wanted?

Highlighted
Partner
Partner

Re: Help With Set Expression - Syntax Error (and Possible Logic Error)

It is indeed. I’m probably doing something wrong. Give me a few minutes to download your qvw and compare against mine.

Highlighted
Partner
Partner

Re: Help With Set Expression - Syntax Error (and Possible Logic Error)

Ok.  My fault for not explaining fully.  Template Order ID is a calculated dimension and thus gets a syntax error when I enter the expression, even in a text box (Bad field name(s)).  I can export the data without this expression, select the Template Order ID field and remove duplicates and wind up with 644.  What I would like to do is have the field, let's call it Distinct Template Order IDs, and then that field be a field where I can sum it.  So, if it's not distinct, the value is 0, otherwise it's 1 (meaning LOB Template Order is a distinct value).

Make sense?

Highlighted

Re: Help With Set Expression - Syntax Error (and Possible Logic Error)

How is it calculated?

Re: Help With Set Expression - Syntax Error (and Possible Logic Error)

I mean how is Template Order ID calculated?

Highlighted
Partner
Partner

Re: Help With Set Expression - Syntax Error (and Possible Logic Error)

The calculated dimension for column Template Order ID is =if(index(,'-')=0,[LOB Template Order]).