topic Re: Need to add more fields to a conditional field in script in QlikView

Need to add more fields to a conditional field in script

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Need to add more fields to a conditional field in script

ahaahaaha — Mon, 26 Jun 2017 12:45:30 GMT

Hi,

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

Regards,

Andrey

Re: Need to add more fields to a conditional field in script

Tue, 27 Jun 2017 01:49:14 GMT

Thank you Andrey,

What should I do in the grouping calculations?

Re: Need to add more fields to a conditional field in script

ahaahaaha — Tue, 27 Jun 2017 08:09:56 GMT

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.

Re: Need to add more fields to a conditional field in script

Wed, 28 Jun 2017 01:31:43 GMT

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