Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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

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

Highlighted

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

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

Highlighted
Partner
Partner

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

 

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???

 

Highlighted
Partner
Partner

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

Reply Sent. Thank you for your patience.

Highlighted

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

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

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

Highlighted
Partner
Partner

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

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.

Highlighted

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

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

Highlighted
Partner
Partner

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

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

Highlighted

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

Like one of theses

Capture.PNG

View solution in original post

Partner
Partner

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

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.