Creating a new field?

[jeni_404]

Hello, I've just picked up Qlik Sense this past month and need help with creating a new field. At the simplest level, I want to create a field called C which is basically just Sum(A). I want to build on this and then create a new field called D which is C/Sum(B). As of right now, I can't even figure out how to create C as a field. I'm just trying to add it as a measure in several graphs/tables.

I've been able to create new Master Items with limited success, but every attempt at adding in these simple calculations into the data load editor have been met with errors. I've tried coding in all of the different ways mentioned on other similar answered questions on here but haven't had luck with any of them. Am I supposed to edit directly in the main script, or create a new section exclusively for field creation? And do new fields automatically show up in the data model viewer?

Any help would be appreciated!

[QFabian]

Hi @jeni_404 , Create new fields is a thing to do at load editor lever (script). Assuming that all fields are in the same table.

For example :

Data:
Load
Id,
A,
B,
[morefields]
FROM [YourLib and Data Source];

Result:
Load
Id,
sum(A) as C,
sum(B) as B_Sum,
sum(A) / sum(B) as D
Resident Data
group By Id;

//Then in yor chart expression you can use :
sum(D) //or
sum(C) / sum(B_Sum)

[jeni_404]

Thanks for the response!

The letters were just place holders for my field names, I realized it might be helpful if I showed what I have so far. Here's the part of the load editor script that's giving me trouble, lines 39 and 40 are both erroring when I try to create the new fields, but lines 34-36 work just fine creating them using the 'as' assigner. Also I think I'm a little confused by the difference between the "Data" and "Result" codes you wrote, are these supposed to go in two separate places within the script? And what does the "Id" line do? I tried adding this into my script as well but was met with another error.

[QFabian · Accepted solution]

When You have aggregation in  your load, You need to put group By . 

So, after use your load without  39 and 40 lines, you can create another table using resident command, to use the data previously loaded.

//Example:

Result:

Load

OrderID,

sum(Cost) as Margin,

sum(Cost) / sum(Sales) as MarginPercent

Resident Data// table label líne 26

group By 

OrderID;

[rwunderlich · Accepted solution]

as @QFabian said, if you use Sum() in your load, you will need to add Group By. However, in this case I don't think you want or need Sum. You only have one Cost  and Sales value per row. I think lines 39 & 40 should be:

Sales - Cost as Margin,
Cost / Sales - 1  as MarginPercent,

-Rob

[jeni_404]

Ah ok both of these solutions work for me now, thank you so much!