Use Group by base on TGB_Name

[iahmadmca1]

Team,

Just I want to take one help regarding group by TGB Name.I have listed down the below script based on this script I need to use group by TGB_Name could you please help me to do this.

Details:

LOAD OperatingDate as oprating_date,

      TGB_ID as TGBID,

     TGB_Name as TGBName,

     ENCLOSURE as Enclosure,

     ALARMS as Alarm,

     IPADDR as IPAddress,

     FIRST_ALERT as First_Alert,

     LATEST_ALERT as Latest_Alert,

     COUNTER as Counter,

     EVENT_TIME as Event_time

       FROM

(qvd) ;

Below TGB Name details.

TGB_Name
Elkview_DA (PECO_AC PECOD) Newlinville_DA (PECO_AC PECOD) Pools_DA (PECO_AC PECOD) Elkview_AMI (PECO) Newlinville_AMI (PECO) Pools_AMI (PECO) METER_SHOP_1733_TEST (PECOTEST) BerwynMeterShop_AMI_TX2 (PECO)

Thanks,

Irshad Ahmad

[PrashantSangle]

you can use group by clause when you are using aggregating any field..

like

Load TGB_Name as TGBName,

count(COUNTER) as Counter

from table_name

group by TGB_Name ;

Regards

[iahmadmca1]

Thanks Prashant for the quick Reply,

When I am using this script based on your suggestion it is throwing the error. please refer the below error screenshots.

Thanks,

Irshad Ahmad

[prma7799]

You have to use aggregation to all fields .

[PrashantSangle]

What is ur end output??

As prma7799‌ said you need add all non -aggregated fields in group by clause

Regards,

[prma7799]

Please share some sample data.

Or  try like below

Details:

LOAD

   OperatingDate as oprating_date,

      TGB_ID as TGBID,

     TGB_Name as TGBName,

     ENCLOSURE as Enclosure,

     ALARMS as Alarm,

     IPADDR as IPAddress,

     FIRST_ALERT as First_Alert,

     LATEST_ALERT as Latest_Alert,

     COUNTER as Counter,

     EVENT_TIME as Event_time

       FROM

(qvd) ;

Test:

LOAD

     TGBID ,

     TGB_Name

Resident Details

Group by TGBID ,TGB_Name

;

[Kushal_Chawda]

Details:

LOAD OperatingDate as oprating_date,

     TGB_ID as TGBID,

     TGB_Name as TGBName,

     ENCLOSURE as Enclosure,

     ALARMS as Alarm,

     IPADDR as IPAddress,

     FIRST_ALERT as First_Alert,

     LATEST_ALERT as Latest_Alert,

     COUNTER,

     EVENT_TIME as Event_time

       FROM

(qvd) ;

left join(Details)

LOAD TGBID,

          Count( COUNTER) as Counter

resident Details

group by TGBID;

[neha_shirsath]

Hi,

In load table take only Group by field with which you need to aggregate,

if you are taking all fields then you need to take all listed fields in group by clause and it might take more time to reload.

Thanks,

Neha

[iahmadmca1]

Thanks, Neha.

[iahmadmca1]

Thanks, Kushal