Creating a variable substracting 2 variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Creating a variable substracting 2 variables

I am trying to create a field called Varience that is Actual Payments minus Original Forecast (Trantype). All of my numbers below are made up of a transaction 'amount' with a 'trandate'. I am trying to use my existing data sets to pull in all the Actual Payments, another data set with Original Forecast, then a dataset where I try to create the 'Trantype' of Variance, subtracting a.amount from b.amount. Capture.JPG

Below is an example of what I am trying to do for code. My data is working with the seperate data sets, but when I join and plave into my Qlikview report, I am not getting the results I want. The trantype is not ending up as "Variance".

Untitled.jpg


Accepted Solutions
Solution
‎10-02-2013 02:45 PM
Super Contributor
Posts: 1,636

Re: Creating a variable substracting 2 variables

you should use "merge" instead of "set".

View solution in original post


All Replies
Super User
Posts: 17,784

Re: Creating a variable substracting 2 variables

The code looks correct to me, so its changing somewhere else.

I don't know what you mean by this though:

"but when I join and plave into my Qlikview report"

Super User
Posts: 10,483

Re: Creating a variable substracting 2 variables

Are you getting a message in the log about variable trantype already in the data set? You have 3 versions, one from each input set a and b and the one you create with "as". A quick test with small data sets makes me bet you are getting the trantype as 'ActualPayments'. Drop the trantype from the two input sets and retry.

Occasional Contributor
Posts: 8

Re: Creating a variable substracting 2 variables

Ok let me simplify what I am coming accross right now. I have 2 data sets right now, one has "amount1" and the other "amount2". Both are formatted as numeric and I am looknig for the format to subtract 1 from 2. I thought I knew what it is, but I am not receiving any results. What am I doing wrong?

Capture.JPG

Occasional Contributor
Posts: 8

Re: Creating a variable substracting 2 variables

Sorry in the example it shows I am adding, but this was just a test to see if even that worked.

Solution
‎10-02-2013 02:45 PM
Super Contributor
Posts: 1,636

Re: Creating a variable substracting 2 variables

you should use "merge" instead of "set".

Occasional Contributor
Posts: 8

Re: Creating a variable substracting 2 variables

Thanks Linilin, I was just experimenting with this when I saw your reply. It looks like I am getting the results I want now.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 282 views
  • 1 like
  • 4 in conversation