I've been working hard to understand Qlik, but the documentation is sparse and there simply aren't a ton of scripting examples out there to help me pick it up any faster. I'm very frustrated, so my apologies if I'm not explaining this well.
My data is small, only about 3500 records and 100 columns. It is a single table which I've connected to via an ODBC connection. I need to create a new derived column that will hold values that come from a calculation. I need to accomplish this in the Data Load Editor.
The calculation has been given to me and needs to be translated from this pseudocode into working code:
(sum(Col_A) / total_num__of_records) * value_in_Col_B
In the data load editor, I need to write something like this, but cannot understand why it won't work. The error message literally shows a question mark and no explanation:
LOAD * ,
((SUM(Col_A) / COUNT(Record_ID)) * Col_B) AS New_Column;
That's all I want to do, but it won't work, and despite a ton of googling, I cannot figure out how to perform such a simple calculation in the Data Load Editor. Please help! Thank you!
The script engine is as confused as you are :) jokes.
The aggregation functions in the script require a group by clause for all the fields not in the aggregation. Without the group by the script engine doesn't know how to sum() or count() because it is working line by line and so Sum(A) = A and count(A)=1.
Knowing only what you've told us here this expression makes no sense to do in the Data Load Editor and would be much better served and make perfect sense in the Expression Editor via the front end. There it would be:
((SUM(Col_A) / COUNT(total Record_ID)) * Col_B)
This gives you Col_B multiplied by the percentage of transactions flagged with a 1. So probably a percentage of tasks completed or something similar.
To do it in the Data Load Editor you need to create a temp table with your group bys only to create the same then you can never display that ratio at any other level but what you defined in the group by so you will lose all the power of the Qlik front end.
Something like this: (I just added DEPARTMENT to show that all fields not in the sum(), count() need to be in the group by. This includes Col_B assuming you want a New_Column value for each value of Col_B per DATE per DEPARTMENT)
DAILY_COMP_RATIOS:
load
DATE,
DEPARTMENT,
((SUM(Col_A) / COUNT(Record_ID)) * Col_B) AS New_Column
resident
SOURCE_TABLE
group by
DATE,
DEPARTMENT,
Col_B;