where clause on a specific field?

[#1]

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?

[vishsaggi]

No you cant use where clause before from.

May be this:

LOAD Company,

          ID,

          Name,

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

FROM yourdatasource;

[#3]

ive currently got

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

but it says invalid expression?

[vishsaggi]

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)

[#5]

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;

[zebhashmi]

you can use "Exist"

[vishsaggi]

Add detail_type in your Group By and run.

Resident Sales_Order

Group by AlphaProdID, detail_type;

[#8]

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.

[#9]

Ill try it out

[vishsaggi]

Sounds like a plan.