calculation in Script from two sources

[#1]

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

[ramoncova06]

can you add the loading script including the left join piece

[#3]

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

[#4 · Accepted solution]

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;

[#5]

Hi Ramon,

please find attached the qvw-file.

Chris

[ramoncova06]

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;

[#7]

Hi Ramon,

thank you I will try this as well.

Chris

[#8]

Hi Brent,

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

We will see what will work

Chris