Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to do Group by for cusotm field with if condition

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,

5 Replies
Not applicable
Author

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;


talk is cheap, supply exceeds demand
calvindk
Creator III
Creator III

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

Not applicable
Author

Sorry it did not group

Not applicable
Author

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