Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
Hi Gareth,
you really need to upload your QVD file.
Andy
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
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
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
It is not possible to refer to an expression field in another expression.
replace UNIT.GRADEFIX_SCORE with if( UNIT.GRADEFIX = '', 2,UNIT.GRADEFIX)
Thanks everyone, this has been solved now