topic Re: Creating a new field? in App Development

Creating a new field?

jeni_404 — Sat, 16 Nov 2024 00:24:24 GMT

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!

Re: Creating a new field?

QFabian — Fri, 19 Mar 2021 01:50:07 GMT

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)

Re: Creating a new field?

jeni_404 — Fri, 19 Mar 2021 02:42:09 GMT

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.

Re: Creating a new field?

QFabian — Fri, 19 Mar 2021 03:55:34 GMT

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;

Re: Creating a new field?

rwunderlich — Fri, 19 Mar 2021 04:17:45 GMT

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

Re: Creating a new field?

jeni_404 — Fri, 19 Mar 2021 15:50:32 GMT

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