Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

garethct
Contributor

Creating new field

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

1 Solution

Accepted Solutions
qlikmsg4u
Valued Contributor

Re: Creating new field

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

7 Replies
fchukoskie
Contributor

Re: Creating new field

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
Esteemed Contributor

Re: Creating new field

Hi Gareth,

you really need to upload your QVD file.

Andy

qlikmsg4u
Valued Contributor

Re: Creating new field

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

Not applicable

Re: Creating new field

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

Not applicable

Re: Creating new field

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
Honored Contributor III

Re: Creating new field

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

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


garethct
Contributor

Re: Creating new field

Thanks everyone, this has been solved now