topic Re: If in Script in QlikView

If in Script

Anonymous — Wed, 15 Jul 2015 12:38:18 GMT

This is my script,

load

%_BNO,

if(UNIT.GRADEFIX = '' , '', num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) + Avg(UNIT.CAPCLEAN_ORIG /1000))) as Buyer.GCT_Score

FROM

[$(QVD_Path)\BUYER_unit.qvd]

(qvd)

group by %_BNO;

It's again saying invalid expression?

Thanks

Gareth

Re: If in Script

alexandros17 — Wed, 15 Jul 2015 12:40:49 GMT

You need an aggregation function for

if(UNIT.GRADEFIX = '' , '', num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) + Avg(UNIT.CAPCLEAN_ORIG /1000))) as Buyer.GCT_Score

something like

Avg(

if(UNIT.GRADEFIX = '' , '', num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) + Avg(UNIT.CAPCLEAN_ORIG /1000))) as Buyer.GCT_Score

)

... even if this expression (the one I've written) doesn't work

Re: If in Script

sunny_talwar — Wed, 15 Jul 2015 12:43:32 GMT

Try this:

Load

%_BNO,

if(UNIT.GRADEFIX = '' , '', num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) + Avg(UNIT.CAPCLEAN_ORIG /1000))) as Buyer.GCT_Score

FROM

[$(QVD_Path)\BUYER_unit.qvd]

(qvd)

Group By %_BNO, UNIT.GRADEFIX;

Re: If in Script

Anonymous — Wed, 15 Jul 2015 13:15:44 GMT

Thanks sunindia,

What I'm trying to do is if the avg(Unit.Gradefix = '' then make it a 2 otherwise use avg(unit.gradefix)

My expression is below however I don't think it's correct.

Load

%_BNO,

if(Avg(UNIT.GRADEFIX = '' , 2 + num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) + Avg(UNIT.CAPCLEAN_ORIG /1000))

num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) + Avg(UNIT.CAPCLEAN_ORIG /1000))) as Buyer.GCT_Score

FROM

[$(QVD_Path)\BUYER_unit.qvd]

(qvd)

Group By %_BNO, UNIT.GRADEFIX;

Re: If in Script

sunny_talwar — Wed, 15 Jul 2015 13:31:53 GMT

Not sure I understand, do you have a sample we can look at?