How to do Group by for cusotm field with if condition

[#1]

Please help me here. how can i achieve group by clasue for custom fields.

LOAD DivisionKey,
     Division,
     Region ,
     SubsidiaryKey,
     Subsidiary,
    if ( SubsidiaryKey = 'HHR','A1',CategoryKey) as CategoryKey,
    if ( SubsidiaryKey = 'HHR','Oral Care',Category) as Category,
    if ( SubsidiaryKey = 'HHR','1Z',SubCategoryKey) as SubCategoryKey,
    if ( SubsidiaryKey = 'HHR','Mixed oral Care',SubCategory) as SubCategory, 

   
   
     sum([Inventory Value]) as [Inventory Value] ,
   
Resident [Inventory Raw_Tmp]

Group by DivisionKey,
     Division,
     Region,
     SubsidiaryKey,
     Subsidiary,
     if ( SubsidiaryKey = 'HHR','A1',CategoryKey) as CategoryKey,
    if ( SubsidiaryKey = 'HHR','Oral Care',Category) as Category,
    if ( SubsidiaryKey = 'HHR','1Z',SubCategoryKey) as SubCategoryKey,
    if ( SubsidiaryKey = 'HHR','Mixed oral Care',SubCategory) as SubCategory,     
    //CategoryKey,
    //Category,
    // SubCategoryKey,
    // SubCategory,

[#2]

u can't.

first use if then take resident load and do a group by

Regards

[Gysbert_Wassenaar]

Just use the original field names of the source table (Inventory Raw_Tmp in your case) in the group by.

LOAD DivisionKey,
     Division,
     Region ,
     SubsidiaryKey,
     Subsidiary,
     if ( SubsidiaryKey = 'HHR','A1',CategoryKey) as CategoryKey,
     if ( SubsidiaryKey = 'HHR','Oral Care',Category) as Category,
     if ( SubsidiaryKey = 'HHR','1Z',SubCategoryKey) as SubCategoryKey,
     if ( SubsidiaryKey = 'HHR','Mixed oral Care',SubCategory) as SubCategory,

     sum([Inventory Value]) as [Inventory Value]

Resident [Inventory Raw_Tmp]

Group by DivisionKey,
     Division,
     Region,
     SubsidiaryKey,
     Subsidiary,
     CategoryKey,
     Category,
     SubCategoryKey,
     SubCategory;

[calvindk]

You shuold not need the custom field in your group by, as it is only using information you are already grouping by.

So this should be fine:

LOAD DivisionKey,
     Division,
     Region ,
     SubsidiaryKey,
     Subsidiary,
    if ( SubsidiaryKey = 'HHR','A1',CategoryKey) as CategoryKey,
    if ( SubsidiaryKey = 'HHR','Oral Care',Category) as Category,
    if ( SubsidiaryKey = 'HHR','1Z',SubCategoryKey) as SubCategoryKey,
    if ( SubsidiaryKey = 'HHR','Mixed oral Care',SubCategory) as SubCategory, 

   
   
     sum([Inventory Value]) as [Inventory Value]
   
Resident [Inventory Raw_Tmp]

Group by DivisionKey,
     Division,
     Region,
     SubsidiaryKey,
     Subsidiary

[#5]

Sorry it did not group

[#6]

it does not fail, but it does not group results in one row.