Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ibradly
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
ibradly
Contributor III
Contributor III
Author

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
Digvijay_Singh

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.

ibradly
Contributor III
Contributor III
Author

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

Digvijay_Singh

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'.

Digvijay_Singh

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
ibradly
Contributor III
Contributor III
Author

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?

Digvijay_Singh

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.

 

Digvijay_Singh

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

ibradly
Contributor III
Contributor III
Author

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

 

Capture.PNG

Here is the sample of the data

 
 
 
Digvijay_Singh

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

;