Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

DIV

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?

11 Replies
Highlighted
MVP
MVP

Re: DIV

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.

Highlighted
Creator III
Creator III

Re: DIV

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

Highlighted

Re: DIV

May be try

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

...

Or

Sum (field1)/sum (field2) as field

Highlighted
Creator III
Creator III

Re: DIV

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]

Highlighted
MVP
MVP

Re: DIV

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 ;
Highlighted
Creator III
Creator III

Re: DIV

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 ...;

Highlighted

Re: DIV

May be this.. instead of join

Table :

Load yourfields,

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

Source;

Highlighted
Creator III
Creator III

Re: DIV

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)

Highlighted
MVP
MVP

Re: DIV

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.