Script/expression help

[#1]

Hi,

We are wanting to put complaints numbers into bands for analysis purposes. We do this quite often with things like Age and the following expression works everytime:

However, when we have tried to replicate with another field - complaint ID Qlikview keeps saying invalid expression and won't load the view- as you can see if it gives us no error prompt when in the script:

Load
[Complaint ID] as [ASB Complaint ID],
    IF(Count(DISTINCT([Complaint ID])) <1,'1. 0-1 Cases') as ASB_Banding, 
    [Complaint Date] as [Date ASB Complaint made against tenant],
    [Complaint Type] as [Type of ASB allegedly perpetrated],
    [Tenancy No];

SQL SELECT *
FROM qlfdat.dbo."QLIKVIEW_ASB";

Any help would be appreciated.

Chris

[alexpanjhc]

It is hard to tell from the script, but i'd comment out the [Complaint ID] just to see if the error is persistent for the next field. If it does, is the database connected? Otherwise, check the table to see what happened to the ID?

[carolin01]

If a similar case like yours and got is working like this:

if(OnTimeDeliveryInDays  = 0, '6. On Time -5 / + 1 Day',
if(OnTimeDeliveryInDays  = '-1', '6. On Time -5 / + 1 Day',
if(OnTimeDeliveryInDays  = '-2', '6. On Time -5 / + 1 Day',
if(OnTimeDeliveryInDays  = '-3', '6. On Time -5 / + 1 Day',
if(OnTimeDeliveryInDays  = '-4', '6. On Time -5 / + 1 Day',
if(OnTimeDeliveryInDays  = '-5', '6. On Time -5 / + 1 Day',
if(OnTimeDeliveryInDays  = '1', '6. On Time -5 / + 1 Day',
if(OnTimeDeliveryInDays  > '1', '4. More Days of delay',
if(OnTimeDeliveryInDays  < '-5', '1. Earlier'))))))))) as OnTimeDeliveryMinus5Plus1Day,

Instead of and use a second if Statement. In your third row you also state if(Tenancy_Length_Years<1. <-- there is a Point behind the 1.

[carolin01]

if(Tenancy_Length_Years < 1, '2. 0-1 years',

if(Tenancy_Length_Years > 1,
if(Tenancy_Length_Years < 2, '2. 1-2 years',
if(Tenancy_Length_Years> 2,
if(Tenancy_Length_Years<3, '3. 2-3 years',
if(Tenancy_Length_Years> 3,
if(Tenancy_Length_Years<5, '4. 3-5 years',
if(Tenancy_Length_Years>5,
if(Tenancy_Length_Years<10, '5. 5-10 years',

if(Tenancy_Length_Years>10,
if(Tenancy_Length_Years<20, '6. 10-20 years', '7. 20 years +'))))))))))) as Tenancy_Length_Years_Branding

[Mark_Little]

HI,

Aggregation is the problem i think.

You have a count in your script, so it is expecting a group by clause.

So i reckon you will need to load the data from SQL,

The do a resident of the table

Like...

Load

....

Resident Table

Group BY Field;

Mark

[sunny_talwar]

I think the problem is that you are using Count() function which is a type of aggregation and every aggregation requires a Group By statement.  I am not sure what the required output is, but you need to add all non-aggregating fields into your group by statement. I hope that make sense

[alex_millan]

Hi,

I agree with them, problem here is the aggregation function. But, moreover, I can't see the point of this field.

Complaint ID, isn't it a unique id value within your data model?

And regarding your expression:

     IF(Count(DISTINCT([Complaint ID])) <1,'1. 0-1 Cases') as ASB_Banding, 

How these count expression could be evaluated less than 1 for a Complaint ID that you're loading? Assuming  that this expression could work, at least it will be evaluated as 1 for all rows, isn't it?

[sasikanth]

Hi,

As suggested by the people add all non aggregated fields in group by statement

OR

If you donot have any performance issues,

then you can  create the same in front end with out any issues