topic Re: Distinct in set analysis in QlikView

Distinct in set analysis

Fri, 09 May 2014 16:07:50 GMT

Hi guys,

I am trying to convert the following sql

SELECT Distinct ID FROM invoiceLine" where number is null

to a set analysis expression.

Can you please help me with it?

Saurabh

Re: Distinct in set analysis

alexandros17 — Fri, 09 May 2014 16:12:31 GMT

You are melting Script and layout environments, when data is loaded it can be diplayed in a wide variety of ways

depending on your dimension and expression.

Set Analysis is related to the way data is displayed and data must be "aggregated" in some way!

Hope it helps

Re: Distinct in set analysis

Fri, 09 May 2014 16:21:18 GMT

how about this

select ID, count(*) from invoiceline where where number is null group by ID...

here we have aggregate function...

Re: Distinct in set analysis

Jason_Michaelides — Fri, 09 May 2014 16:26:41 GMT

For use in the script?

Re: Distinct in set analysis

Fri, 09 May 2014 16:31:44 GMT

no in expression....

Re: Distinct in set analysis

rwunderlich — Fri, 09 May 2014 17:04:52 GMT

You can do it either with a search expression or the indirect E() operator. Here it is with E().

{$<ID = E({1<number={"*"}>})>}

-Rob

Re: Distinct in set analysis

Joseph_Musekura — Fri, 09 May 2014 17:13:29 GMT

Set Analysis

Sets can be used in aggregation functions. Aggregation functions normally aggregate over the set of possible records defined by the current selection. But an alternative set of records can be defined by a set expression. Hence, a set is conceptually similar to a selection.

As mentioned in the first response, you need to check what you want to achieve and make a difference with SELECT in SQL and the use of SET ANALYSIS

Regards,

Re: Distinct in set analysis

Fri, 09 May 2014 19:44:46 GMT

This is what I am trying

Count(DISTINCT {$<ID= E(1<number={"*"}>)>})

But it gives me error "Error in set modifier element function" at curly brace after "number="

Is there any thing here?

Re: Distinct in set analysis

maxgro — Fri, 09 May 2014 19:59:58 GMT

count(distinct RobSuggestion ID)

example: =count(distinct {$<id = P({1<bonus={"*"}>})>} sales)