topic Re: Script/expression help in QlikView

Script/expression help

Mon, 08 Feb 2016 13:20:09 GMT

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

Re: Script/expression help

alexpanjhc — Mon, 08 Feb 2016 13:27:36 GMT

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?

Re: Script/expression help

carolin01 — Mon, 08 Feb 2016 13:28:59 GMT

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.

Re: Script/expression help

carolin01 — Mon, 08 Feb 2016 13:38:12 GMT

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

Re: Script/expression help

Mark_Little — Mon, 08 Feb 2016 13:44:12 GMT

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

Re: Script/expression help

sunny_talwar — Mon, 08 Feb 2016 13:46:16 GMT

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

Re: Script/expression help

alex_millan — Mon, 08 Feb 2016 13:49:27 GMT

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?

Re: Script/expression help

sasikanth — Mon, 08 Feb 2016 13:52:45 GMT

Hi,

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

If you donot have any performance issues,

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