Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create two new fields in the data load editor section but having trouble doing so. I basically want to select all from a table I have and create two new aggregated fields which I can use in charts.
Below is current attempt i have but getting errors in doing so.
SQL select *
,sum([Jan-22]+ [Feb-22]+ [Mar-22]+ [Apr-22] +[May-22]+[Jun-22]+[Jul-22]+ [Aug-22]+ [Sep-22]+ [Oct-22] +[Nov-22]+[Dec- 22]) as TOTAL22
,sum([Jan-23]+ [Feb-23]+ [Mar-23]+ [Apr-23] +[May-23]+[Jun-23]+[Jul-23]+ [Aug-23]+ [Sep-23]+ [Oct-23] +[Nov-23]+[Dec- 23]) AS TOTAL23
from [vw].[CBP_View_TF];
This is SQL, not Qlik script. That said, using the syntax of Select * , Field is not allowed in some databases so that might be your issue.
When I was using the select * it was working for me and i could create charts based off fields. Is there a way to create new fields in the Qlick script editor or does this need to be done back in the view. Would prefer to keep it in the app if possible.
Load *, Field1+Field2 as SomeFieldName;
Select Field1, Field2
From SomeTable;
I am trying the below but still getting errors on it. Do you know where i have gone wrong.
SQL LOAD *
,sum([Jan-22]+ [Feb-22]+ [Mar-22]+ [Apr-22] +[May-22]+[Jun-22]+[Jul-22]+ [Aug-22]+ [Sep-22]+ [Oct-22] +[Nov-22]+[Dec-22]) AS TOTAL22 + sum([Jan-23]+ [Feb-23]+ [Mar-23]+ [Apr-23] +[May-23]+[Jun-23]+[Jul-23]+ [Aug-23]+ [Sep-23]+ [Oct-23] +[Nov-23]+[Dec-23]) AS TOTAL23;
SELECT TOTAL22, TOTAL23
from [vw].[CBP_View_TF];
You've put an SQL before the Load statement. Load is not SQL, it's Qlik script. You've also not selected the fields in the SQL part of the statement so you won't be able to use them in the Load part of the statement. Finally, you've used Sum() but there's no GROUP BY statement, so what exactly are you summing by? If you're just trying to add the fields, you don't need sum().
LOAD *
,([Jan-22]+ [Feb-22]+ [Mar-22]+ [Apr-22] +[May-22]+[Jun-22]+[Jul-22]+ [Aug-22]+ [Sep-22]+ [Oct-22] +[Nov-22]+[Dec-22]) AS TOTAL22 + ([Jan-23]+ [Feb-23]+ [Mar-23]+ [Apr-23] +[May-23]+[Jun-23]+[Jul-23]+ [Aug-23]+ [Sep-23]+ [Oct-23] +[Nov-23]+[Dec-23]) AS TOTAL23;
SQL SELECT *
from [vw].[CBP_View_TF];
Sorry I thought I needed to state where my data source was originally from. The two fields I was trying to create would be a sum of those fields so I thought an aggregate function was required. I am trying your solution but still no luck. Getting error around the sign.
A load statement pulls data from the statement directly following it, unless explicitly told otherwise (through the use of e.g. Resident).
Looks like you have a plus where you should have a comma. I copy-pasted your code and didn't notice that.
Yes this works perfect. Thanks for all the help in solving it and also explaining how the process works for me.