Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

creating calculated fileds from 2 tables

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;

10 Replies
MVP
MVP

Re: creating calculated fileds from 2 tables

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

Re: creating calculated fileds from 2 tables

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;

MVP
MVP

Re: creating calculated fileds from 2 tables

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,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: creating calculated fileds from 2 tables

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


Not applicable

Re: creating calculated fileds from 2 tables

Thanks...changed that but still not working

MVP
MVP

Re: creating calculated fileds from 2 tables

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.

Not applicable

Re: creating calculated fileds from 2 tables

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;

MVP
MVP

Re: creating calculated fileds from 2 tables

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

Not applicable

Re: creating calculated fileds from 2 tables

Ok so I need to put Resident AFVC ?

Community Browser