Announcements
cancel
Showing results for
Did you mean:
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?

Chris

1 Solution

Accepted Solutions
Not applicable
Author

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:
FROM sometable;

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

JOIN

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:

FROM RESIDENT Table1Tmp;

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

DROP TABLE1Tmp;

7 Replies
Specialist III

Not applicable
Author

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.

Chris

Not applicable
Author

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:
FROM sometable;

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

JOIN

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:

FROM RESIDENT Table1Tmp;

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

DROP TABLE1Tmp;

Not applicable
Author

Hi Ramon,

Chris

Specialist III

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:

*,

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
Author

Hi Ramon,

thank you I will try this as well.

Chris

Not applicable
Author

Hi Brent,

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

We will see what will work

Chris

Community Browser