creating calculated fileds from 2 tables

[#1]

I have 2 tables in my load script that I want to create some calulated fields from. I have tried to create a resident load but it doesn't work

This is what I have at the moment

MLST:

LOAD AUFPL as operationno,
APLZL as counter,
AUFPL+APLZL as opno,
MLST_ZAEHL as milestoneno,
Right (MLST_ZAEHL,5) as milestone,
MLSTN as usage,
TEDAT as scheduleddate,
LST_ACTDT as actualdate,
if((TEDAT>today()),TEDAT) as FutureMilestones,
if(TEDAT>Today()and TEDAT <Today()+35,TEDAT)as '5WeekOverview',
if(TEDAT>=WeekStart(today(),0,-1)-7 and TEDAT<=WeekStart(today(),0,-1),TEDAT)as PrevWk,
if(TEDAT < Today() and LST_ACTDT = '',TEDAT) as CurrrentDelinquent



FROM

(qvd)

where MLSTN = 'N0003';

left join
LOAD ZZMLST_ZAEHL1 as milestoneno,
ZZMLST_TEDAT1 as Customerbaseline

FROM

(qvd);

Calctable:
left join MLST:
load
actualdate,
scheduleddate,
customerbaseline,
if(actualdate > customerbaseline and scheduleddate > customerbaseline and customerbaseline > '',scheduleddate) as completeddelinquent,
if(actualdate = '' and scheduleddate > customerbaseline, scheduleddate) as projectdelinquent
Resident MLST;

[swuehl]

QV is case sensitive, you have use customerbaseline in your condition, but Customerbaseline in the second load.

[alexandros17]

Just do:

Calctable:

NoConcatenate

//left join MLST:
load
actualdate,
scheduleddate,
customerbaseline,
if(actualdate > customerbaseline and scheduleddate > customerbaseline and customerbaseline > '',scheduleddate) as completeddelinquent,
if(actualdate = '' and scheduleddate > customerbaseline, scheduleddate) as projectdelinquent
Resident MLST;

[jonathandienst]

Qlikview field names are case sensitive. It looks like you are trying to join on customerbaseline, but this will not join to Customerbaseline.

In your last load statement, rename customerbaseline:

     customerbaseline As Customerbaseline,

[#5]

Thanks

I've taken that script but I still get an error that says table not found

Table not found
Calctable:

NoConcatenate

load
actualdate,
scheduleddate,
customerbaseline,
if(actualdate > customerbaseline and scheduleddate > customerbaseline and customerbaseline > '',scheduleddate) as completeddelinquent,
if(actualdate = '' and scheduleddate > customerbaseline, scheduleddate) as projectdelinquent
Resident MLST

[#6]

Thanks...changed that but still not working

[swuehl]

Are you loading a table with the same number of fields and field names as in table load MLST prior your script snippet?

This will auto-concatenate MLST table to your existing table.

[#8]

This is what I have prior to MLST

AFVC:

LOAD AUFPL as operationno,
PROJN as projectno,
AUFPL+APLZL as opno


FROM

(qvd)

where PROJN > 0;


left join
MLST:
LOAD AUFPL as operationno,
APLZL as counter,
AUFPL+APLZL as opno,
MLST_ZAEHL as milestoneno,
Right (MLST_ZAEHL,5) as milestone,
MLSTN as usage,
TEDAT as scheduleddate,
LST_ACTDT as actualdate,
if((TEDAT>today()),TEDAT) as FutureMilestones,
if(TEDAT>Today()and TEDAT <Today()+35,TEDAT)as '5WeekOverview',
if(TEDAT>=WeekStart(today(),0,-1)-7 and TEDAT<=WeekStart(today(),0,-1),TEDAT)as PrevWk,
if(TEDAT < Today() and LST_ACTDT = '',TEDAT) as CurrrentDelinquent



FROM

(qvd)

where MLSTN = 'N0003';

left join
LOAD ZZMLST_ZAEHL1 as milestoneno,
ZZMLST_TEDAT1 as customerbaseline

FROM

(qvd);

Calctable:

NoConcatenate

//left join MLST:
load
actualdate,
scheduleddate,
customerbaseline,
if(actualdate > customerbaseline and scheduleddate > customerbaseline and customerbaseline > '',scheduleddate) as completeddelinquent,
if(actualdate = '' and scheduleddate > customerbaseline, scheduleddate) as projectdelinquent
Resident MLST;

[swuehl]

Seems like you're left joining your MLST table to table AFVC, so there is only a resulting table AFVC.

[#10]

Ok so I need to put Resident AFVC ?