topic Re: Replace a field value subject to a specific combinations of fields in QlikView

Replace a field value subject to a specific combinations of fields

chriys1337 — Thu, 02 Feb 2017 10:07:46 GMT

Hi,

I have a task in front of me, where the table looks like: ID, Country and Place.

So if the Country is a specific value like "GERMANY" and the Place is "Munich", I would like to create a new field with the value "GER South".

I have tried this with Group by:

Test:
Load #GEO_ID as ID,
Country_Name as Country_Name_test,
Place_Name as Place_Name_test,
if(Country_Name= 'GERMANY' and Place_Name_test= 'Munich', 'GER South',Country_Name) as Country_Name_new
Resident [Geo From]
group by ID, Country_Name_test,Place_Name_test,;

but there is a error occurring, "Aggregation expressions required by GROUP BY clause", so I guess my group by idea is not working out.

Does anybody have an idea how to handle this?

Re: Replace a field value subject to a specific combinations of fields

sunny_talwar — Thu, 02 Feb 2017 10:09:32 GMT

You don't need Group By here since you are not doing any Sum() or Count() in your load.... try just this:

Test:
LOAD #GEO_ID as ID,
     Country_Name as Country_Name_test,
     Place_Name as Place_Name_test,
     If(Country_Name = 'GERMANY' and Place_Name = 'Munich', 'GER South', Country_Name) as Country_Name_new
Resident [Geo From];

Re: Replace a field value subject to a specific combinations of fields

surendraj — Thu, 02 Feb 2017 10:11:58 GMT

In above you didn't use any aggregations right?

then why you specified Group by clause.it is not required.

Re: Replace a field value subject to a specific combinations of fields

chriys1337 — Thu, 02 Feb 2017 10:23:47 GMT

Thank you both, sometimes the solution is so easy :-). QlikView is such a great tool!