Skip to main content
Announcements
Get Ready. A New Qlik Learning Experience is Coming February 17! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
iahmadmca1
Contributor III
Contributor III

Use Group by base on TGB_Name

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

11 Replies
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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
iahmadmca1
Contributor III
Contributor III
Author

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.

group.jpg

Thanks,

Irshad Ahmad

prma7799
Master III
Master III

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
prma7799
Master III
Master III

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
Specialist
Specialist

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

Thanks, Neha.

iahmadmca1
Contributor III
Contributor III
Author

Thanks, Kushal