Calculated variables in load editor

[RowdyBoy9]

Hi Coders,

I'm new to Qlik Sense and trying to figure out how to create calculated fields while data is being loaded in load editor. I have excel file with below fields &  calculation is defined below.

Score Level: =IFS(B2="L", 1, B2 = "M", 2, B2="H", 3) - This I have got in load editor, No action needed here.

I need below fields to be calculated in load editor.

Count: =COUNTIF(A:A,A2)

Max: =MAX(D:D)

Min: =MIN(D:D)

Vol: =D2/($E$2-$F$2)+1

Avg: =SUMIF(A:A,A2,C:C)/COUNTIF(A:A,A2)

Score: =H2*G2

Avg of Score - Can we get the Average of Score field mentioned above. I need to achieve below pivot after all fields are calculated and loaded.

Is this doable in Qlik Sense Load editor ? if Yes, can you guys please help me out in achieving this output.

I've included excel file also for your reference. any help will be really appreciated.

Thanks in advance,

Rowdy

[sidhiq91]

@RowdyBoy9  Please see below that I have created in the load script editor. 

NoConcatenate
Temp:
LOAD
Controls,
Level,
"Score Level",
"Count",
"Max",
"Min",
Vol,
"Avg",
Score
FROM [lib://Qlik Community Practice/Control.xlsx]
(ooxml, embedded labels, table is Sheet1);

Max:
Load
Max(Count) as Max_Count
Resident Temp;

Min:
Load
Min(Count) as Min_Count
Resident Temp;

Vmax=Peek('Max_Count',0,'Max');
Vmin=Peek('Min_Count',0,'Min');

NoConcatenate
Temp1:
Load Controls,
Count(Controls) as [Count_of_Controls]
Resident Temp
group by Controls;

left join (Temp1)
Temp2:
Load Controls,
Avg(Vol*Avg) as [Avg of Scores]
Resident Temp
group by Controls;

left join (Temp1)
Temp3:
Load Controls,
(Sum(Count)/count(Controls)) as [Average of Avg]
Resident Temp
group by Controls;

left join (Temp1)
Temp4:
Load Controls,
Avg((Count)/('$(Vmax)'-'$(Vmin)')+1) as [Average of Vol]
Resident Temp
group by Controls;

Drop table Temp,Max,Min;

Exit Script;

[sidhiq91]

@RowdyBoy9  if this resolves your issue, please like and accept it as a solution.

[RowdyBoy9]

Hi @sidhiq91 Thank you for your quick response, I really appreciate it. I see there is no Count calculated in the script. I'm getting below error.

The following error occurred:

Field 'Count' not found

The error occurred here:

Max: Load Max(Count) as Max_Count Resident Open

I want to calculate 'Count, Max, Min, Vol, Avg & Score' fields in the load editor script, not just loading from excel source file. Because, my actual source data doesn't have any of  these fields, I've manually added those formulated columns in the excel and shared as sample file.

I believe, you just used the existing formulas of excel file and created a table view. Maybe my query isn't clear for you. I want those fields from (Count, Max, Min, Vol, Avg & Score) need to be calculated, not just loading it from source file. My actual raw data doesn't have these fields.

could you please share how to get these as per the criteria mentioned in the initial post.

Thanks

Rowdy

[sidhiq91]

@RowdyBoy9  here we go! This should sort out your issue. If this resolves issue please like and accept it as a solution.

NoConcatenate
Temp:
LOAD
Controls,
Level,
"Score Level",
//"Count",
"Max",
"Min",
Vol,
"Avg",
Score
FROM [lib://Qlik Community Practice/Control.xlsx]
(ooxml, embedded labels, table is Sheet1);

left join (Temp)

Load Controls,
Count(Controls) as Count
Resident Temp
group by Controls;

Max:
Load
Max(Count) as Max_Count
Resident Temp;

Min:
Load
Min(Count) as Min_Count
Resident Temp;

Vmax=Peek('Max_Count',0,'Max');
Vmin=Peek('Min_Count',0,'Min');

NoConcatenate
Temp1:
Load Controls,
Count(Controls) as [Count_of_Controls]
Resident Temp
group by Controls;

left join (Temp1)
Temp2:
Load Controls,
Avg(Vol*Avg) as [Avg of Scores]
Resident Temp
group by Controls;

left join (Temp1)
Temp3:
Load Controls,
(Sum(Count)/count(Controls)) as [Average of Avg]
Resident Temp
group by Controls;

left join (Temp1)
Temp4:
Load Controls,
Avg((Count)/('$(Vmax)'-'$(Vmin)')+1) as [Average of Vol]
Resident Temp
group by Controls;

Drop table Temp,Max,Min;

Exit Script;

[RowdyBoy9]

Hi @sidhiq91, I'm getting error as

The following error occurred:

Field 'vol' not found

As I mentioned earlier to this post, I need to calculate formulas for all the fields not just Count. I need calculation for Vol, Avg & Score as well.

The above code resolved the calculation for Count, Max & Min fields. I need calculation for vol, avg & score as well.

would you please help  me out.

Thanks in advance.

[sidhiq91]

@RowdyBoy9  Apologies for the delayed response, was sick. Please find below script for all the variables and this should sort out your issue.

NoConcatenate
Temp:
LOAD
Controls,
Level,
"Score Level"
//"Count",
// "Max",
//"Min",
//Vol,
// "Avg",
//Score
FROM [lib://Qlik Community Practice/Control.xlsx]
(ooxml, embedded labels, table is Sheet1);

left join (Temp)

Load Controls,
Count(Controls) as Count
Resident Temp
group by Controls;

Max:
Load
Max(Count) as Max_Count
Resident Temp;

Min:
Load
Min(Count) as Min_Count
Resident Temp;

Vmax=Peek('Max_Count',0,'Max');
Vmin=Peek('Min_Count',0,'Min');

Left join (Temp)
Load Controls,
Avg([Score Level]) as Avg
Resident Temp
group by Controls;

Left Join (Temp)
Load Controls,
(Sum(Count)/Sum(('$(Vmax)'-'$(Vmin)')))+1 as Vol
Resident Temp
group by Controls;

Left Join (Temp)
Load Controls,
Level,
sum(Vol*Avg) as Score
Resident Temp
group by Controls,Level;

NoConcatenate
Temp1:
Load Controls,
Count(Controls) as [Count_of_Controls]
Resident Temp
group by Controls;

left join (Temp1)
Temp2:
Load Controls,
Avg(Vol*Avg) as [Avg of Scores]
Resident Temp
group by Controls;

left join (Temp1)
Temp3:
Load Controls,
Avg(Avg) as [Average of Avg]
Resident Temp
group by Controls;

left join (Temp1)
Temp4:
Load Controls,
Avg((Count)/('$(Vmax)'-'$(Vmin)')+1) as [Average of Vol]
Resident Temp
group by Controls;

Drop table Temp,Max,Min;

Exit Script;

[RowdyBoy9]

Hi @sidhiq91 , No worries. I have figured it out in creating the calculated fields in the load editor script and thank you for your solution as well.

Cheers!!