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: 
pnn44794
Partner - Specialist
Partner - Specialist

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.

27 Replies
pnn44794
Partner - Specialist
Partner - Specialist
Author

LOB Template Order is calculated / determined within the Load Scrip with the following: Mid(BRN, 3, 7) as ,

sunny_talwar

I am not really sure I am able to follow you....

pnn44794
Partner - Specialist
Partner - Specialist
Author

 

And that’s my fault.  Let me, if you don’t mind, try to be more clear and again my fault for not being so.

 

I hope this makes more sense???

 

pnn44794
Partner - Specialist
Partner - Specialist
Author

Reply Sent. Thank you for your patience.

sunny_talwar

This clarifies it all (I think )... Can you DISTINCT count LOB Template Order in that case?

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

pnn44794
Partner - Specialist
Partner - Specialist
Author

This does give me the 644 total which is correct when put in a text box. An additional problem is that I’m using a straight table. So, each row that has a unique LOB Template Order can have a unique Line Item. That means when I export the data and do a simple Sum, I get a total of 1,875. 1,875 should be the total items ordered from the 644 LOB Template Orders placed. Is there a way to say, for example, I have a unique LOB Template Order with one Line Item so that I would get a total of 644? That may not make sense.

In other words, I only want to count one Line Item for each unique LOB Template Order. If more than one Line Item per LOB Template Order, set the value to zero for the rest. That way, when it gets summed, I get a total of 644. Here’s a screenshot of what I’m trying to explain.

sunny_talwar

Don't see any screenshot attached, but what is your dimension? I used this as a dimension (just like you mentioned)

Dimension

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

Expression

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

And I only see 644 rows? Attaching the Excel with this response

Capture.PNG

pnn44794
Partner - Specialist
Partner - Specialist
Author

Sorry.  I sent it via email and apparently that doesn't work.

sunny_talwar

Like one of theses

Capture.PNG

pnn44794
Partner - Specialist
Partner - Specialist
Author

I believe this does it, but a couple of questions.  It looks like the difference between the two straight tables is under the Presentation tab.  For the straight table on the left, you have Suppress Zero-Values checked which just gives the 644 unique LOB Template Orders, I believe and for the straight table on the right, Suppress Zero-Values is not checked which returns all rows, but only puts a 1 in for the first unique LOB Template Order.  Is that right?  No other differences?

Secondly, and this may simply be how Excel works, when I export either table out to Excel, the 1's export as text.  Do we need to specifically format the 1's as a number?

Thank you so much for your help.