Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: How to do Group by for cusotm field with if condition

u can't.

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

Regards

Gysbert_Wassenaar
Not applicable

Re: How to do Group by for cusotm field with if condition

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
Not applicable

Re: How to do Group by for cusotm field with if condition

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

Re: How to do Group by for cusotm field with if condition

Sorry it did not group

Not applicable

Re: How to do Group by for cusotm field with if condition

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