Dimension based on multiple criteria / values in other dimensions

[Anonymous]

Hello everybody,

I want to create a New dimension called  Freight TYPE  with two values : Dedicated and MIXED.

Dedicated freight  is any freight with only one type of Customer Group under Section TYPE
“ OUT”  , so that: Freight 1: would be classified as mixed and Freight 2 as dedicated.

The end result would be:

Customer
Group              Freight.TYPE        Cost

A                     Mixed                       300    

A                   Dedicated                   200

Data looks somewhat like below:

Freight #             Section.Type                     Customer Group1            COST

1                                             OUT                                      A                     50

1 OUT B 100

1                                             BH                                         X                     150

2                                             OUT                                      A                      100   

2                                             BH                                         Z                      100      

I was  trying different ways in calculated dimension combaining  AGGR, TOTAL, Count(Distinct) and iF

But none of it worked.

Big thanks in advance to anyone who can solve it !

[#2]

Adam,

may you provide qvw with sample data?

pozdrawiam

Darek

[#3]

Ok, i can see data, give me a moment

[#4]

Example for you

Let me know if it helps.

regards

Darek

[sudeepkm · Accepted solution]

As per your requirement logic It looks likes one Customer Group can be either Dedicated or Mixed based on the Section type of the Freight#.

From the Data given below it looks like

Group A for Freight 1 is Mixed as the Freight 1 is having two records with Section Type as OUT. however

Group A for Freight 2 is Dedicated as there is only one record with Section Type as OUT.

I've attached an example where the Freight Type is calculated at  a script level.

Freight #            Section.Type                    Customer Group1            COST

1                                            OUT                                      A                    50

1                                            OUT                                      B                    100

1                                            BH                                        X                    150

2                                            OUT                                      A                      100  

2                                            BH                                        Z                      100

[Anonymous]

Dariusz,

Thanks, but would it be possible to incorporate distinct after count?

The real data is much more complex as  there are more dimensions relating to the customer group such as customer point. There are many drop points for one customer group relating to particular  freight . So  I need to be able to use distinct somehow.

[Anonymous]

Sudeep,

I will check this and will let you know shortly.

[#8]

Of course i'am sorry ...

use:

=aggr(if(count( DISTINCT total <Group> Freight)=1,'Dedic','Mix'),Group)

[Anonymous]

Dariusz,

But we are counting only groups for   section type- 'OUT' ( another dimension).

Could you add that condition?

I put below in the expression in my model, and all is returned as dedicated: Did I get it right?

Aggr(if(count(DISTINCT TOTAL <CustomerGroup> FreightNum & Freights_Plachta.SectionType='OUT')=1,'Dedic','Mix'),CustomerGroup)

[#10]

Adam,

Please, privide qvw with your data model and sample data

Regards

Darek

25-04-2014 13:34 użytkownik "Adam Safita" <qcwebmaster@qlik.com>

napisał:

Qlik Community <http://community.qlik.com/> Dimension based on

multiple criteria / values in other dimensions

reply from Adam Safita<http://community.qlik.com/people/asafita77?et=watches.email.thread>in *App

Development* - View the full discussion<http://community.qlik.com/message/516060?et=watches.email.thread#516060>