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

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

pnn44794 — Sun, 13 Oct 2019 00:09:58 GMT

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.

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

sunny_talwar — Sun, 03 Dec 2017 22:09:13 GMT

May be this

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

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

sunny_talwar — Sun, 03 Dec 2017 22:09:51 GMT

Or this

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

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

pnn44794 — Sun, 03 Dec 2017 22:51:16 GMT

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

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

sunny_talwar — Sun, 03 Dec 2017 22:52:24 GMT

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

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

pnn44794 — Sun, 03 Dec 2017 22:55:18 GMT

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

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

pnn44794 — Sun, 03 Dec 2017 23:05:56 GMT

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?

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

sunny_talwar — Sun, 03 Dec 2017 23:15:26 GMT

How is it calculated?

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

sunny_talwar — Sun, 03 Dec 2017 23:15:45 GMT

I mean how is Template Order ID calculated?

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

pnn44794 — Sun, 03 Dec 2017 23:21:06 GMT

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

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

pnn44794 — Sun, 03 Dec 2017 23:22:54 GMT

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

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

sunny_talwar — Sun, 03 Dec 2017 23:33:25 GMT

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

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

pnn44794 — Sun, 13 Oct 2019 00:02:40 GMT

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

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

pnn44794 — Mon, 04 Dec 2017 00:00:42 GMT

Reply Sent. Thank you for your patience.

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

sunny_talwar — Mon, 04 Dec 2017 00:06:02 GMT

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

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

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

pnn44794 — Mon, 04 Dec 2017 01:09:43 GMT

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.

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

sunny_talwar — Mon, 04 Dec 2017 01:15:42 GMT

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

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

pnn44794 — Sun, 13 Oct 2019 00:04:37 GMT

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

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

sunny_talwar — Mon, 04 Dec 2017 12:25:31 GMT

Like one of theses

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

pnn44794 — Mon, 04 Dec 2017 15:03:31 GMT

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.