topic Re: Expression In Script in QlikView

Expression In Script

Anonymous — Mon, 16 Nov 2015 19:54:58 GMT

I have the below expression in the chart properties and I would like to add in the scrip. Can someone show me the best approach?

Expression in Chart - count({<Status={[Pended]}>}distinct ID)

how would that look in the scrip? Can I use a IF Statement, this is what I have below...

Count(IF(Status= 'Pended','')) as TotalPends

Re: Expression In Script

sunny_talwar — Mon, 16 Nov 2015 19:58:36 GMT

May be something like this:

LOAD Count(Status),

ChartDimensions

Resident ...

Group By Chart Dimensions

Where Status = 'Pended';

Re: Expression In Script

MK_QSL — Mon, 16 Nov 2015 19:59:03 GMT

Yes but don't forget to use Group By in your script..

Re: Expression In Script

Anonymous — Mon, 16 Nov 2015 20:07:58 GMT

Count(IF(Status= 'Pended',distinct ID ) as TotalPends

I want to count distinct ids when a status = pended

Not sure how that would look in the script

Re: Expression In Script

MK_QSL — Mon, 16 Nov 2015 20:13:54 GMT

Data:

Load * From TableName;

Count:

Load COUNT(Distinct ID) as TotalCount;

Load ID Resident Data Where Status = 'Pended';

Re: Expression In Script

sunny_talwar — Mon, 16 Nov 2015 20:17:05 GMT

And if there is a particular dimension on which you want the count, then may be this:

Data:

Load * From TableName;

Count:

Load Dimension,

COUNT(Distinct ID) as TotalCount;

Load ID Resident Data

Group By Dimension

Where Status = 'Pended';

Re: Expression In Script

Anonymous — Mon, 16 Nov 2015 20:30:42 GMT

I'm hoping the expression can be created within the initial load with Ifs

I have...

Table:

Load

xxxx,xxxx,xxx,xxx

//(Hundereds of Columns)

Count(distinct ids) as TotalIds,

Count(DISTINCT if(Status='Pended')) as TotalPends , (The syntax is not right)

xxxx

FROM

Group by

xxxx,xxxx,xxx,xxx xxxx;

Re: Expression In Script

sunny_talwar — Mon, 16 Nov 2015 20:33:04 GMT

I think it would be better to left join your result back to the original table, because I don't think this would work the way you are doing:

1st of all are you doing a overall count of ids where status is pending? or on a particular dimension or dimensions?

Re: Expression In Script

Anonymous — Mon, 16 Nov 2015 20:38:14 GMT

I'm trying to remove the expression that's in my chart count({<Status={[Pended]}>}distinct ID) and add into my script as an If statement but it seems that's not the best approach and may not be possible.

Re: Expression In Script

sunny_talwar — Mon, 16 Nov 2015 20:39:27 GMT

What are the dimensions you are using in the chart?

Re: Expression In Script

Anonymous — Mon, 16 Nov 2015 20:43:06 GMT

My dimensions are, Status & ID. Count the # of ID when my status = pended.

I have about 30 other calculations that I need to create in the script. Keeping as simple as I can.

Re: Expression In Script

sunny_talwar — Mon, 16 Nov 2015 21:03:38 GMT

Would you be able to share a sample?

Re: Expression In Script

Anonymous — Tue, 17 Nov 2015 15:13:08 GMT

In a straight table without expression embedded in the chart I would like to see.

See attached example. I need to see (distinct)totals for the two columns that I created TotalPend and TotalId

DESCRIPTION	ID	STATUS	COUNTstatus	TOTALPENDS
TOTAL			7	4
FIVE	5	PENDING	1	1
FOUR	4	DENIED	1	0
ONE	1	PENDING	1	1
SEVEN	7	PENDING	1	1
SIX	6	APPROVED	1	0
THREE	3	PENDING	1	1
TWO	2	APPROVED	1	0

Re: Expression In Script

Anonymous — Tue, 17 Nov 2015 15:15:30 GMT

Added an example

Re: Expression In Script

sunny_talwar — Tue, 17 Nov 2015 15:16:02 GMT

Did you forget to add data to the table here?

Re: Expression In Script

Anonymous — Tue, 17 Nov 2015 15:22:45 GMT

ADDED DATA

Re: Expression In Script

sunny_talwar — Tue, 17 Nov 2015 15:27:38 GMT

This?

Re: Expression In Script

Anonymous — Tue, 17 Nov 2015 15:39:19 GMT

Yes but I do not want to create expressions. There's not a way to create in the script?

Re: Expression In Script

sunny_talwar — Tue, 17 Nov 2015 15:44:09 GMT

Looking for something like this?

Script:

TEST:

LOAD * Inline [

ID, DESCRIPTION, STATUS

1, ONE, PENDING

2, TWO, APPROVED

3, THREE, PENDING

4, FOUR, DENIED

5, FIVE, PENDING

6, SIX, APPROVED

7, SEVEN, PENDING

];

Join(TEST)

LOAD Count(STATUS) AS COUNTstatus

Resident TEST;

Join(TEST)

LOAD Count(STATUS) AS COUNTPENDS

Resident TEST

Where STATUS = 'PENDING';

Re: Expression In Script

Anonymous — Tue, 17 Nov 2015 15:50:30 GMT

No, I assumed this would be a standard question but maybe I'm not being clear enough. I need what you have in your first example but without the expression in the chart. Is there not a way to create the expression in the script???