Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Group by clause returning 0 for calculated fields

Hi,

Can someone please explain to me why my total impressions and impressionslostrank is giving me 0 when I do a group by clause?

Fact:
NoConcatenate
load
Date,
Device,
Engine,
Category,
Campaign,
IsBrand,
IsCoreBrand,
Program,
Geo_Modified,
Country,
Region,
Clicks,
Impressions,
Cost,
SharedDMA,
ClickToCalls,
SIS,
SISLost,
sum(Impressions)/ sum(SIS) as TotalImpressions,
(sum(Impressions)/ sum(SIS)) * sum(SISLost) as ImpressionsLostRank,
Forms, 
DNI_Calls,
resident DCM_Temp3
group By
Date,
Device,
Engine,
Category,
Campaign,
IsBrand,
IsCoreBrand,
Program,
Geo_Modified,
Country,
Region,
Clicks,
Impressions,
Cost,
SharedDMA,
ClickToCalls,
SIS,
SISLost,
Forms, 
DNI_Calls;

Sorry for the long code.

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

Re: Group by clause returning 0 for calculated fields

I found the problem. I had two databases merged together and one of them did that not have device segment. That's why it was breaking.
Sorry for the back and forth. I appreciate your help.

View solution in original post

10 Replies
Highlighted
Master III
Master III

Re: Group by clause returning 0 for calculated fields

First thing I would say is you should not have SIS, SISLost and impression fields in the group by clause. These fields you are using in aggregation functions.

Highlighted
Contributor III
Contributor III

Re: Group by clause returning 0 for calculated fields

I tried that and it gave me invalid expression. So I am not sure what is happening exactly in the back end.

Highlighted
Master III
Master III

Re: Group by clause returning 0 for calculated fields

Can you share sample data? I see too many fields in Group By clause but that shouldn't be a problem as long as you have fields in Group By clause having repetitive values, normally these are dimension fields, do you think cost needs to be there in group By clause? 

Do you get any error while script load? If its getting executed properly, I don't see anything wrong if you don't have zeroes in the fields used inside 'SUM'.

Highlighted
Master III
Master III

Re: Group by clause returning 0 for calculated fields

The comma after last field in the table load is not allowed I think, are you sure your script is executed or you have right statement in qlik and its just typo here?

Forms,
DNI_Calls,
resident DCM_Temp3
Highlighted
Contributor III
Contributor III

Re: Group by clause returning 0 for calculated fields

The comma after DNI Calls is just a typo here. There is no error when i run it in the script loader. However, there are some campaigns that have 0 impressions. You are saying that could be the error?

This is a shorter script of the original dataset.

DCS:
Load
date,
Campaign,
AdGroup,
Engine,
DeviceSegment,
Impressions,
Clicks,
Cost,
SIS,
SISLost,
sum(Impressions)/ sum(SIS) as TotalImpressions,
(sum(Impressions)/ sum(SIS)) * sum(SISLost) as ImpressionsLostRank
resident DCSTemp
group By
date,
Campaign,
AdGroup,
Engine,
DeviceSegment;

When i run this scrip it gives me invalid expressions. When I add impressions, clicks, cost, SIS and SISLost to the group by it runs. Why is that the case?

Highlighted
Master III
Master III

Re: Group by clause returning 0 for calculated fields

This script will throw error because each field needs to be mentioned either in the aggregation function or below the group by clause.

I don't see Clicks, Cost used in any of these places. Either you should remove from the field list or mention in the group by clause but it all based on your needs.

 

Highlighted
Master III
Master III

Re: Group by clause returning 0 for calculated fields

I don't think 0 impressions in some rows will be a problem here. Can you share some sample rows.

Highlighted
Contributor III
Contributor III

Re: Group by clause returning 0 for calculated fields

This is the error even after i add clicks and cost to the group by.

 

Capture.PNG

Here is the sample of the data

 
 
 
Highlighted
Master III
Master III

Re: Group by clause returning 0 for calculated fields

I think script is still wrong, try like this - 

DCS:
Load
date,
Campaign,
AdGroup,
Engine,
DeviceSegment,
Clicks,
Cost,
sum(Impressions)/ sum(SIS) as TotalImpressions,
(sum(Impressions)/ sum(SIS)) * sum(SISLost) as ImpressionsLostRank
resident DCSTemp
group By
date,
Campaign,
AdGroup,
Engine,
DeviceSegment,

Clicks,
Cost

;