Need to add more fields to a conditional field in script

[#1]

Hi,

I am struggling with this for a while so I thought asking for some help.

What I want to accomplish below is to create the ClientSize field based on the total value of each TopAccount.

This is working very well, however I want to add more fields to the ClientSize such as Region and Segment so I can get more stats.

The tricky part is that if I add them in the ClientSize group by table, then I get duplicates.

What would you recommend I should use here? ClientSize should always be evaluated with the total value of each TopAccount but the Region, Segment, are defined at AccountName.

Data1:

Load

  %Date,

  TopAccount,

     AccountName,

     AccountId,

     Value,

     [Product Line],

     Product_Category__c,

     Product_Sub_Category__c,

     Region,

     Segment

     FROM

     [..\QVD\Extract\Data.qvd] (qvd)

;

//Grouping by Top Account to create Client Size and Product Count

ClientSize:

Load

  %Date,

  TopAccount,

  count(DISTINCT([Product Line])) as Count_ProductLines,

  sum(Value)  as Value

  resident Data1

  Group by  %Date,TopAccount

  ;

Products:

Load

  %Date,

  TopAccount,

  count(DISTINCT(Product_Sub_Category__c)) as Count_ProductSubCategory,

  sum(Value)  as Value

  resident Data1

  Group by  %Date,TopAccount

  ;

Outer Join(ClientSize)

Load

  *

  resident Products

  ;

Concatenate(Data1)

Load

  %Date,

  TopAccount,

  Count_ProductLines,

    Count_ProductSubCategory,

  Value,

  if(Value<50000,'Equal or less than $50k',

      if(Value < 100000,'Between $50k and less than $100k',    

      if(Value < 250000,'Between $100k and less than $250k',

      if(Value< 500000,'Between $250k and less than $500k',

      if(Value < 1000000,'Between $500k and less than $1M','Equal or greater than $1M'))))) as ClientSize,

    if(Count_ProductLines=1,'No. of Clients buying only from 1 Product Line',

      if(Count_ProductLines =2,'No. of Clients buying only from 2 Product Lines',    

      if(Count_ProductLines = 3,'No. of Clients buying only from 3 Product Lines',

      if(Count_ProductLines = 4,'No. of Clients buying only from 4 Product Lines',

      'No. of Clients buying from All Product Lines')))) as ProductLineCount,

     1 as  TotalFlag

resident  ClientSize;

drop tables ClientSize, Products;

Any suggestions? many thanks in advance

Marcelo

[ahaahaaha]

Hi,

Can be either part of the grouping calculations or in all, transfer everything to the level of charts (UI)?

Regards,

Andrey

[#3]

Thank you Andrey,

What should I do in the grouping calculations?

[ahaahaaha]

If in a simplified form, then every added dimension is the grouping by this dimension (see the example and comments in the attached file).

As you can see in the attached file tables 2 and 3 are the same. Table 2 uses grouping at the script level, but table 3 at level UI.

[#5 · Accepted solution]

Thank you,

I just solved it by creating more groupings in the script and adding Flags so to have correct totals across

see below

//Grouping by Top Account to create Client Size and Product Count

ClientSize:

Load

  %Date,

  TopAccount,

  count(DISTINCT([Product Line])) as Count_ProductLines,

  sum(Value)  as Value

  resident Data1

  Group by  %Date,TopAccount

  ;

Products:

Load

  %Date,

  TopAccount,

  count(DISTINCT(Product_Sub_Category__c)) as Count_ProductSubCategory,

  sum(Value)  as Value

  resident Data1

  Group by  %Date,TopAccount

  ;

Outer Join(ClientSize)

Load

  *

  resident Products

  ;

Concatenate(Data1)

Load

  %Date,

  TopAccount,

  Count_ProductLines,

    Count_ProductSubCategory,

  Value,

  if(Value<50000,'Equal or less than $50k',

      if(Value < 100000,'Between $50k and less than $100k',   

      if(Value < 250000,'Between $100k and less than $250k',

      if(Value< 500000,'Between $250k and less than $500k',

      if(Value < 1000000,'Between $500k and less than $1M','Equal or greater than $1M'))))) as ClientSize,

    if(Count_ProductLines=1,'No. of Clients buying only from 1 Product Line',

      if(Count_ProductLines =2,'No. of Clients buying only from 2 Product Lines',   

      if(Count_ProductLines = 3,'No. of Clients buying only from 3 Product Lines',

      if(Count_ProductLines = 4,'No. of Clients buying only from 4 Product Lines',

      'No. of Clients buying from All Product Lines')))) as ProductLineCount,

     1 as  TotalFlag

resident  ClientSize;

drop tables ClientSize, Products

//Grouping by Top Account and Region to create Client Size and Product Count

ClientSizeRegion:

Load

  %Date,

  TopAccount,

  Region,

  count(DISTINCT([Product Line])) as Count_ProductLines,

  sum(Value)  as Value

  resident Data1

  Group by  %Date,TopAccount, Region

  ;

ProductsRegion:

Load

  %Date,

  TopAccount,

  Region,

  count(DISTINCT(Product_Sub_Category__c)) as Count_ProductSubCategory,

  sum(Value)  as Value

  resident Data1

  Group by  %Date,TopAccount,Region

  ;

Outer Join(ClientSizeRegion)

Load

  *

  resident ProductsRegion

  ;

Concatenate(Data1)

Load

  %Date,

  TopAccount,

Region,

  Count_ProductLines,

    Count_ProductSubCategory,

  Value,

  if(Value<50000,'Equal or less than $50k',

      if(Value < 100000,'Between $50k and less than $100k',   

      if(Value < 250000,'Between $100k and less than $250k',

      if(Value< 500000,'Between $250k and less than $500k',

      if(Value < 1000000,'Between $500k and less than $1M','Equal or greater than $1M'))))) as ClientSize,

    if(Count_ProductLines=1,'No. of Clients buying only from 1 Product Line',

      if(Count_ProductLines =2,'No. of Clients buying only from 2 Product Lines',   

      if(Count_ProductLines = 3,'No. of Clients buying only from 3 Product Lines',

      if(Count_ProductLines = 4,'No. of Clients buying only from 4 Product Lines',

      'No. of Clients buying from All Product Lines')))) as ProductLineCount,

     1 as  TotalFlag

resident  ClientSizeRegion;

drop tables ClientSizeRegion, ProductsRegion

Thanks,

Marcelo