topic DIV in QlikView

DIV

Anonymous — Thu, 19 Nov 2015 16:29:20 GMT

I have the following

IF(turned = 'Yes',1,0) as [Countofturned],

IF(Initial = 'Denied',1,0)as [CountofDenied],

Div([Countofturned],[CountofDenied]) as [Percent]

Getting an error Countofturned does not exist. How can I write this in the script?

Re: DIV

swuehl — Thu, 19 Nov 2015 16:44:30 GMT

Maybe in a preceding LOAD?

LOAD *,

Div([Countofturned],[CountofDenied]) as [Percent];

LOAD

IF(turned = 'Yes',1,0) as [Countofturned],
IF(Initial = 'Denied',1,0)as [CountofDenied],
...
FROM ...;

You cannot reference field names in a LOAD that were created using AS in the same LOAD statement.

Re: DIV

Anonymous — Thu, 19 Nov 2015 18:15:31 GMT

This is what I have so far, receiving an error.

table:

load allcolumns, IF(turned = 'Yes',1,0) as [Countofturned],
IF(Initial = 'Denied',1,0)as [CountofDenied]

tableA:
Join(table)
load Div([CountofOverturned],[CountofDenied]) as [Percent], ID
Resident table
group by ID ;

I'm gettting the below error, thoughts?

Aggregation expressions required by GROUP BY clause

Re: DIV

settu_periasamy — Thu, 19 Nov 2015 18:36:28 GMT

May be try

Div (sum (field1),sum (field2)) as field

...

Sum (field1)/sum (field2) as field

Re: DIV

Anonymous — Thu, 19 Nov 2015 18:40:17 GMT

Need to SUM if then divide. See below.

IF(turned = 'Yes',1,0) as [Countofturned],

IF(Initial = 'Denied',1,0)as [CountofDenied],

Div([Countofturned],[CountofDenied]) as [Percent]

Re: DIV

maxgro — Thu, 19 Nov 2015 18:43:37 GMT

you need an aggregation function (sum, count, etc...) for the fields not in group by

Join (table)
load

Sum(CountofOverturned) / sum(CountofDenied) as [Percent of Initial Denial Now Approved],

ID
Resident table
group by ID ;

Re: DIV

Anonymous — Thu, 19 Nov 2015 19:41:33 GMT

Can I use a Resident table for my from. So,

LOAD *,

Div([Countofturned],[CountofDenied]) as [Percent];

LOAD

IF(turned = 'Yes',1,0) as [Countofturned],
IF(Initial = 'Denied',1,0)as [CountofDenied],
...
Resident ...;

Re: DIV

settu_periasamy — Thu, 19 Nov 2015 22:20:19 GMT

May be this.. instead of join

Table :

Load yourfields,

Div (if (turned='Yes',1,0),if (turned='Denied',1,0) as percent

Source;

Re: DIV

Anonymous — Fri, 20 Nov 2015 18:40:42 GMT

I do not have this issues resolved. I have the following set analysis, that works but trying to add this into my script. Any thoughts?

How can I add the below into a readable script?

count({<turned={[Yes]}>}distinct ID)/count({<Initial={[Denied]}>}AuthID)

Re: DIV

swuehl — Fri, 20 Nov 2015 18:48:07 GMT

You would need to perform an aggregation in the script.

Note that aggregations in the script will be static, not responding on user selections.

You need to load your tables with a GROUP BY clause and do filtering in WHERE clause, or IF() statement within your aggregation function.

To tell you more, you would need to tell us, in which tables turned, Initial, ID and AuthID fields are located and how these tables are linked.

Re: DIV

Anonymous — Fri, 20 Nov 2015 18:56:45 GMT

Fields are all in one single table and the primary key is ID.

Re: DIV

swuehl — Fri, 20 Nov 2015 19:27:29 GMT

Something like

LOAD

YourGroupByField,

Count(DISTINCT If( turned = '[Yes]', ID )) / Count( If(Initial = '[Denied]', AuthID)) as Quotient

RESIDENT YourTable

GROUP BY YourGroupByField;

Replace YourGroupByField with the field(s) that you want to use to group the Quotient (like your dimensions on front end).

If this is not working, please post a sample QVW. It's hard to help you with only limited understanding of your setting and data.

topic DIV in QlikView

DIV

Re: DIV

Re: DIV

Re: DIV

Re: DIV

Re: DIV

Join (table)load

Sum(CountofOverturned) / sum(CountofDenied) as [Percent of Initial Denial Now Approved],

IDResident tablegroup by ID ;

Re: DIV

Re: DIV

Re: DIV

Re: DIV

Re: DIV

Re: DIV

Join (table)
load

ID
Resident table
group by ID ;