topic Re: where clause on a specific field? in QlikView

where clause on a specific field?

Wed, 25 Nov 2020 16:16:04 GMT

Is it possible to use a where clause on just a certain field?

Load Company,

ID,

Name,

Value where Name = 'Bob'

From ....

Something like the above, where the where clause is before the from clause. The script editor is underlining it in red, but is there a way to do this?

Re: where clause on a specific field?

vishsaggi — Tue, 20 Jun 2017 20:48:56 GMT

No you cant use where clause before from.

May be this:

LOAD Company,

ID,

Name,

IF(Name = 'Bob', Value) AS NewValue

FROM yourdatasource;

Re: where clause on a specific field?

Tue, 20 Jun 2017 20:54:06 GMT

ive currently got

If(detail_type = 'CM', count(so_id)) as CM_count

but it says invalid expression?

Re: where clause on a specific field?

vishsaggi — Tue, 20 Jun 2017 20:56:27 GMT

Yes, coz if you use Count() which is an aggr function you should use Group by in your script.

Else you can create a flag and use that flag in your chart expression. Like

IF(Detail_type = 'CM', 1) AS Cm_Count

Then in your expr:

= sum(CM_Count)

Re: where clause on a specific field?

Tue, 20 Jun 2017 20:57:35 GMT

Here is the full script, it says invalid. I have sales order table loaded above

Aggregate_Data:

Load AlphaProdID,

detail_type,

count(so_id)/count(DISTINCT so_id) as LinesPerOrder,

Count(so_id)/count(DISTINCT SO_detail_created_by) as LinesPerUser,

count(distinct so_id)/count(DISTINCT SO_detail_created_by) as OrdersPerUser,

If(detail_type = 'CM', count(so_id)) as CM_count

Resident Sales_Order

Group by AlphaProdID;

Re: where clause on a specific field?

zebhashmi — Tue, 20 Jun 2017 20:59:16 GMT

you can use "Exist"

Re: where clause on a specific field?

vishsaggi — Tue, 20 Jun 2017 20:59:32 GMT

Add detail_type in your Group By and run.

Resident Sales_Order

Group by AlphaProdID, detail_type;

Re: where clause on a specific field?

Tue, 20 Jun 2017 21:06:23 GMT

Tried this but it messed up my other fields, im assuming because its grouping by 2 fields. Perhaps if i make another table, and just put the single item in and group by it.

Re: where clause on a specific field?

Tue, 20 Jun 2017 21:06:36 GMT

Ill try it out

Re: where clause on a specific field?

vishsaggi — Tue, 20 Jun 2017 21:18:19 GMT

Sounds like a plan.

Re: where clause on a specific field?

its_anandrjs — Tue, 20 Jun 2017 21:29:35 GMT

Try with this code

Aggregate_Data:

Load AlphaProdID,detail_type,

Count(so_id) / Count(DISTINCT so_id) as LinesPerOrder,

Count(so_id) / Count(DISTINCT SO_detail_created_by) as LinesPerUser,

Count(Distinct so_id) / count(DISTINCT SO_detail_created_by) as OrdersPerUser,

If(detail_type = 'CM', count(so_id)) as CM_count

count( If(detail_type = 'CM', so_id ) ) as CM_count

Resident Sales_Order

Group by AlphaProdID,detail_type;

Regards

Anand

Re: where clause on a specific field?

Wed, 21 Jun 2017 13:56:05 GMT

Trying to get the percent of so_id that has CM, so I tried this but it doesn't work

Load AlphaProdID,

detail_type,

count( If(detail_type = 'CM', so_id ) )/count(so_id) as CM_count

Resident Sales_Order

Group by AlphaProdID, detail_type;

Any ideas?

Re: where clause on a specific field?

vishsaggi — Wed, 21 Jun 2017 14:07:00 GMT

Can you share a sample if possible with some sample data?