topic Re: calculation in Script from two sources in QlikView

calculation in Script from two sources

Thu, 29 Oct 2015 15:05:19 GMT

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

Re: calculation in Script from two sources

ramoncova06 — Thu, 29 Oct 2015 15:14:48 GMT

can you add the loading script including the left join piece

Re: calculation in Script from two sources

Thu, 29 Oct 2015 15:18:56 GMT

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

Re: calculation in Script from two sources

Thu, 29 Oct 2015 15:45:59 GMT

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;

Re: calculation in Script from two sources

Thu, 29 Oct 2015 15:47:11 GMT

Hi Ramon,

please find attached the qvw-file.

Chris

Re: calculation in Script from two sources

ramoncova06 — Thu, 29 Oct 2015 18:03:47 GMT

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;

Re: calculation in Script from two sources

Fri, 30 Oct 2015 07:55:22 GMT

Hi Ramon,

thank you I will try this as well.

Chris

Re: calculation in Script from two sources

Fri, 30 Oct 2015 07:56:17 GMT

Hi Brent,

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

We will see what will work

Chris