Discussion Board for collaboration related to Creating Analytics for QlikView.
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
Go to 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, field2FROM sometable;
//Joins the second table - in this case field1 is the common key field
LOAD field1, field3FROM someothertable;
//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
can you add the loading script including the left join piece
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.
please find attached the qvw-file.
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
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]
drop table Assets;
RENAME Table temp to Assets;
thank you I will try this as well.
very much thanks for the Details. I will try this also.
We will see what will work