topic Simple calculation in load script not creating any values in QlikView

Simple calculation in load script not creating any values

thomasmercer — Wed, 25 Nov 2020 16:16:04 GMT

I'm loading a table to pre-summarise then just trying to find the difference between the two so I can pull data from a text bank. I've put together the below loading script which doesn't error however if I use [Avg Score Are] or [Avg Score Want] in my dashboard I get results as expected, but [Avg Diff] doesn't display any results at all.

I think I'm missing something obvious but been stuck on this one for a while!

[PreSummarise]:
LOAD
[Company Name],
[QI],
[Where we],
Avg([Question Score]) AS [Avg Score Are],
Sum([Question Score]) AS [Total Score Are]
Resident EVP.Diagnostic.Survey.TestData1
where [Where we] = 'Are'
and [Company Name] = '$(vCompanyName)'
group by
[Company Name],
[QI],
[Where we];

JOIN
LOAD
[Company Name],
[QI],
[Where we],
Avg([Question Score]) AS [Avg Score Want],
Sum([Question Score]) AS [Total Score Want]
//Sum([Question Score]) - [Total Score Are] AS [Total Dif]
Resident EVP.Diagnostic.Survey.TestData1
where [Where we] = 'Want'
and [Company Name] = '$(vCompanyName)'
group by
[Company Name],
[QI],
[Where we];


[Summarise]:
LOAD	
[Company Name],
[QI],
[Where we],
[Avg Score Want]-[Avg Score Are] as [Avg Diff] //I don't know why this doesn't work
Resident PreSummarise

Re: Simple calculation in load script not creating any values

sunny_talwar — Fri, 16 Nov 2018 17:20:45 GMT

When you join the two tables, remove the join on Where We field because they have different values... the two Averages won't align against each other

[PreSummarise]:
LOAD
[Company Name],
[QI],
[Where we],
Avg([Question Score]) AS [Avg Score Are],
Sum([Question Score]) AS [Total Score Are]
Resident EVP.Diagnostic.Survey.TestData1
where [Where we] = 'Are'
and [Company Name] = '$(vCompanyName)'
group by
[Company Name],
[QI],
[Where we];

JOIN
LOAD
[Company Name],
[QI],
[Where we],
Avg([Question Score]) AS [Avg Score Want],
Sum([Question Score]) AS [Total Score Want]
//Sum([Question Score]) - [Total Score Are] AS [Total Dif]
Resident EVP.Diagnostic.Survey.TestData1
where [Where we] = 'Want'
and [Company Name] = '$(vCompanyName)'
group by
[Company Name],
[QI],
[Where we];


[Summarise]:
LOAD	
[Company Name],
[QI],
[Where we],
[Avg Score Want]-[Avg Score Are] as [Avg Diff] //I don't know why this doesn't work
Resident PreSummarise;

Re: Simple calculation in load script not creating any values

thomasmercer — Fri, 16 Nov 2018 17:29:47 GMT

Excellent! Works a treat. Thanks Stalwar1