Combine 2 rows into one and aggregate some data

[nix1]

When loading data, I want to combine 2 particular rows of data within a larger set of data - so Y1 and Y2 combined 

City	Region	City type	Score 1	Score 2	Score 3
X	South and West Asia	Megacity	1	1	1
Y1	South and West Asia	Megacity	0	0	1
Y2	South and West Asia	Megacity	1	0	0
Z	Africa	Megacity	1	1	1

To get 1 combined row Y, where Min score is 0 and Max score is 1, but if Y1 or Y2 score 1 then Y scores 1 as below:

City	Region	City type	Score 1	Score 2	Score 3
X	South and West Asia	Megacity	1	1	1
Y	South and West Asia	Megacity	1	0	1
Z	Africa	Megacity	1	1	1

I have tried a few things but can't figure it out. Thanks

[ArinaRay]

Hi, @nix1 

You can do it with i"IF" function or ApplyMapp function.
For example,

tmp:
load * Inline
[City_t ,Region_t ,CityType_t ,Score1_t ,Score2_t ,Score3_t
X ,South and West Asia ,Megacity ,1 ,1 ,1
Y1 ,South and West Asia ,Megacity ,0 ,0 ,1
Y2 ,South and West Asia ,Megacity ,1 ,0 ,0
Z ,Africa ,Megacity ,1 ,1 ,1
]
;

Main_tmp:
load
if(City_t='Y1' or City_t='Y2', 'Y', City_t) as City_m
, Region_t as Region_m
, CityType_t as CityType_m
, sum(Score1_t) as Score1_m
, sum(Score2_t) as Score2_m
, sum(Score3_t) as Score3_m
resident tmp
group by
if(City_t='Y1' or City_t='Y2', 'Y', City_t)
, Region_t
, CityType_t
;

Main:
Load
City_m as City
, Region_m as Region
, CityType_m as CityType
, if(Score1_m > 0, 1, 0) as Score1
, if(Score2_m > 0, 1, 0) as Score2
, if(Score3_m > 0, 1, 0) as Score3
Resident Main_tmp
;

drop Table tmp, Main_tmp;

[ArinaRay]

Also you can use ApplyMap instead of IF

tmp:
load * Inline
[City_t ,Region_t ,CityType_t ,Score1_t ,Score2_t ,Score3_t
X ,South and West Asia ,Megacity ,1 ,1 ,1
Y1 ,South and West Asia ,Megacity ,0 ,0 ,1
Y2 ,South and West Asia ,Megacity ,1 ,0 ,0
Z ,Africa ,Megacity ,1 ,1 ,1
]
;

map_City:
Mapping
load * Inline
[City_map, City_new
Y1 ,Y
Y2 ,Y
]
;

Main_tmp:
load
ApplyMap('map_City', City_t, City_t) as City_m
, Region_t as Region_m
, CityType_t as CityType_m
, sum(Score1_t) as Score1_m
, sum(Score2_t) as Score2_m
, sum(Score3_t) as Score3_m
resident tmp
group by
ApplyMap('map_City', City_t, City_t)
, Region_t
, CityType_t
;

Main:
Load
City_m as City
, Region_m as Region
, CityType_m as CityType
, if(Score1_m > 0, 1, 0) as Score1
, if(Score2_m > 0, 1, 0) as Score2
, if(Score3_m > 0, 1, 0) as Score3
Resident Main_tmp
;

drop Table tmp, Main_tmp;