Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

calculation in Script from two sources

Hi,

maybe someone of you can help me.

I loaded data from two different sources into qlikview.

First I loaded a table 'Assets'. Afterwards I loaded the second table via left join (Assets).

Now I needed to create a calculation.

  // ,if(lastdepnyear=0,[Comet (Monate)], if([Comet (Monate)]>((lastdepnyear-CometYear)*12+lastdepnper-CometPer+1),[Comet (Monate)]-((lastdepnyear-CometYear)*12+lastdepnper-CometPer+1),0)) as [RND Comet]

Everything with "Comet..." was within the first load, the "lastdepn..." were part of the second load.

This code does not work. I get the message, that the Comet...-Fields could not be found.

I think it has something to do with "What is already in Memory and what not"

Where in the script do I have to do this? Can you help me with the Syntax?

Thanks in advance

Chris

1 Solution

Accepted Solutions
Not applicable

Re: calculation in Script from two sources

You can do it by joining the two tables in memory into a temp table and then loading the temp table and formula into a final table. Here is some pseudo-code.

//Loads the first table into a temp table (Table1Tmp)

Table1Tmp:
LOAD field1, field2
FROM sometable;

//Joins the second table - in this case field1 is the common key field

JOIN

LOAD field1, field3
FROM someothertable;

NOCONCATENATE

//Your Table1Tmp should have 3 fields now: field1, field2, field3

//Now load Table1Tmp into a final table (NewTable), including a calculation (I used a simple IF statement for example)
NewTable:
LOAD *, If(field2=field3,1,0) AS myCalculation

FROM RESIDENT Table1Tmp;

//Drop the temp table, as it's not needed and will likely mess with wanted relationships

DROP TABLE1Tmp;

7 Replies
ramoncova06
Valued Contributor III

Re: calculation in Script from two sources

can you add the loading script including the left join piece

Not applicable

Re: calculation in Script from two sources

Hi Ramon,

I tried several things but nothing worked.

Would you please write down how it should look like [I am not an IT Guy].

I will try to get the Infos into a qvd and attach this.

Thanks in advance.

Chris

Not applicable

Re: calculation in Script from two sources

You can do it by joining the two tables in memory into a temp table and then loading the temp table and formula into a final table. Here is some pseudo-code.

//Loads the first table into a temp table (Table1Tmp)

Table1Tmp:
LOAD field1, field2
FROM sometable;

//Joins the second table - in this case field1 is the common key field

JOIN

LOAD field1, field3
FROM someothertable;

NOCONCATENATE

//Your Table1Tmp should have 3 fields now: field1, field2, field3

//Now load Table1Tmp into a final table (NewTable), including a calculation (I used a simple IF statement for example)
NewTable:
LOAD *, If(field2=field3,1,0) AS myCalculation

FROM RESIDENT Table1Tmp;

//Drop the temp table, as it's not needed and will likely mess with wanted relationships

DROP TABLE1Tmp;

Not applicable

Re: calculation in Script from two sources

Hi Ramon,

please find attached the qvw-file.

Chris

ramoncova06
Valued Contributor III

Re: calculation in Script from two sources

mmm .... It's weird because I do not see an error when adding the data, I do this by using a binary since I do not have access to the data source

though instead of using a left join I just reloaded the data under a temp and then renamed it

temp:

LOAD

  *,

  if(lastdepnyear=0,[ND Comet], if([ND Comet]>((lastdepnyear-CometYear)*12+lastdepnper-CometPer+1),[ND Comet]-((lastdepnyear-CometYear)*12+lastdepnper-CometPer+1),0)) as [RND Comet] 

Resident Assets;

drop table Assets;

RENAME Table temp to Assets;

Not applicable

Re: calculation in Script from two sources

Hi Ramon,

thank you I will try this as well.

Chris

Not applicable

Re: calculation in Script from two sources

Hi Brent,

very much thanks for the Details. I will try this also.

We will see what will work

Chris

Community Browser