topic Re: creating calculated fileds from 2 tables in QlikView

creating calculated fileds from 2 tables

Thu, 04 Jun 2015 10:40:43 GMT

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;

Re: creating calculated fileds from 2 tables

swuehl — Thu, 04 Jun 2015 10:43:52 GMT

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

Re: creating calculated fileds from 2 tables

alexandros17 — Thu, 04 Jun 2015 10:43:57 GMT

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;

Re: creating calculated fileds from 2 tables

jonathandienst — Thu, 04 Jun 2015 10:45:13 GMT

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,

Re: creating calculated fileds from 2 tables

Thu, 04 Jun 2015 10:53:34 GMT

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

Re: creating calculated fileds from 2 tables

Thu, 04 Jun 2015 10:53:59 GMT

Thanks...changed that but still not working

Re: creating calculated fileds from 2 tables

swuehl — Thu, 04 Jun 2015 11:00:10 GMT

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.

Re: creating calculated fileds from 2 tables

Thu, 04 Jun 2015 11:02:59 GMT

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;

Re: creating calculated fileds from 2 tables

swuehl — Thu, 04 Jun 2015 11:07:36 GMT

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

Re: creating calculated fileds from 2 tables

Thu, 04 Jun 2015 11:09:41 GMT

Ok so I need to put Resident AFVC ?

Re: creating calculated fileds from 2 tables

swuehl — Thu, 04 Jun 2015 11:14:07 GMT

Yes.

And consider that using joins may increase your number of table rows (duplication of lines), if there are 1:n relations. Maybe a mapping approach or just linking tables would be better.