Creating new field

[Anonymous]

Hi all,

I'm having trouble creating a new field in the script my see below,

Load

     UNIT.GRADEFIX,

     if( UNIT.GRADEFIX = '', 2,UNIT.GRADEFIX) as UNIT.GRADEFIX_SCORE,

     num(UNIT.M_MILEAGE / 1000) + UNIT.AGEMONTHS + (UNIT.GRADEFIX_SCORE*10) +  (UNIT.CAPCLEAN_ORIG /1000) as Unit.GCT_Score

FROM

[$(QVD_Path)\BUYER_unit.qvd]

(qvd);

Does anyone have any idea why I'm getting the error cannot find field?

Thanks

[Anonymous]

I think that you'd have to share the QVD in order to answer this.

Make sure that field names match exactly...names are case sensitive in QlikView.

[awhitfield]

Hi Gareth,

you really need to upload your QVD file.

Andy

[qlikmsg4u · Accepted solution]

HI Gareth,

You missing here

Load

    UNIT.GRADEFIX,

    if( UNIT.GRADEFIX = '', 2,UNIT.GRADEFIX) as UNIT.GRADEFIX_SCORE,

    num(UNIT.M_MILEAGE / 1000) + UNIT.AGEMONTHS + (UNIT.GRADEFIX_SCORE*10) +  (UNIT.CAPCLEAN_ORIG /1000) as Unit.GCT_Score

FROM

[$(QVD_Path)\BUYER_unit.qvd]

(qvd);

because you don't have that field in QVD but you just created it in your script, so to calculate Unit.GCT_Score you need to resident the above table

[#5]

Instead of Resident load, you may use the expression again as below

Load

    UNIT.GRADEFIX,

    if( UNIT.GRADEFIX = '', 2,UNIT.GRADEFIX) as UNIT.GRADEFIX_SCORE,

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

FROM

[$(QVD_Path)\BUYER_unit.qvd]

(qvd);

or Load on Load as below

Load

*,

num(UNIT.M_MILEAGE / 1000) + UNIT.AGEMONTHS + (UNIT.GRADEFIX_SCORE*10) +  (UNIT.CAPCLEAN_ORIG /1000) as Unit.GCT_Score;

Load

    UNIT.GRADEFIX,

    if( UNIT.GRADEFIX = '', 2,UNIT.GRADEFIX) as UNIT.GRADEFIX_SCORE

FROM

[$(QVD_Path)\BUYER_unit.qvd]

(qvd);

Regards,

KKR

[#6]

Hi,

You can't create a field and use that field in same load, either you will have to go with resident load or preceding load.

UNIT.GRADEFIX_SCORE field is the one which you are creating and using, which is not allowed. In place of using this value again you can use entire condition there itself.

Regards,

Navdeep

[sasiparupudi1]

It is not possible to refer to an expression field in another expression.

replace UNIT.GRADEFIX_SCORE with if( UNIT.GRADEFIX = '', 2,UNIT.GRADEFIX)

[Anonymous]

Thanks everyone, this has been solved now