Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shwetagupta
Partner - Creator II
Partner - Creator II

If else and Pick match over two dimensional expression

Hi Fellas,

Need your help

VisibilityNorm:

LOAD * INLINE [

    VisibilityNorm, Vis_Product Class, VisNormQty

DGATop8,PREMIUM,2

DGATop8,SUPER PREMIUM,2

DGATop8,MEDIUM,1

DGBNext45,SUPER PREMIUM,2

DGBNext45,PREMIUM,2

DGBNext45,MEDIUM,2

DGRest,PREMIUM,1

DGRest,MEDIUM,1

DGRest,MASS,2

NA,NA,0

SATop8,PREMIUM,1

SATop8,SUPER PREMIUM,1

SATop8,MEDIUM,2

SBNext45,PREMIUM,1

SBNext45,SUPER PREMIUM,1

SBNext45,MEDIUM,1

SBNext45,MASS,1

SRest,PREMIUM,1

SRest,SUPER PREMIUM,1

SRest,MEDIUM,1

SRest,MASS,1

BATop8,PREMIUM,1

BATop8,MEDIUM,1

BBNext45,MEDIUM,1

BBNext45,MASS,1

BRest,MEDIUM,1

BRest,MASS,1

];

I am using this expression

Norm Qty=

If(Match(VisibilityNorm,'DGATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),

if(Match(VisibilityNorm,'DGBNext45'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),

If(Match(VisibilityNorm,'DGRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','2'),

If(Match(VisibilityNorm,'SATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'1','1','2'),

If(Match(VisibilityNorm,'SBNext45'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),

If(Match(VisibilityNorm,'SRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),

If(Match(VisibilityNorm,'BATop8'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM'),'1','1'),

If(Match(VisibilityNorm,'BBNext45'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),

If(Match(VisibilityNorm,'BRest'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),'NA')))))))))*

Count({<AccountChannel={'GT'}, Max_Vis_Flag={'1'}>}Distinct dealer_code_c)

My result looks like this

Vis_Product Class  Norm_Qty

SUPER PREMIUM      -

PREMIUM                    -

MEDIUM                        -

MASS                            -


Now If I select DGATop8 on Visibility Norm filter


Then It shows me the desired result


Vis_Product Class  Norm_Qty

SUPER PREMIUM      2

PREMIUM                    2

MEDIUM                        2

MASS                            -


I need to execute above expression in a way that it should work without filter. I have also tried to Aggr but end up with the same result.


Thanks in advance


16 Replies
sunny_talwar

Are you talking about the green part of the expression? I had to change your expression from what you had to this because the sample data you shared did not have the fields you use within your count function. But to get my point across I had to change that to something else and VirNormQty was the only field that would have worked here...

Is that what confused you or is there something else you don't understand?

shwetagupta
Partner - Creator II
Partner - Creator II
Author

Hi Pradosh,

Thanks for giving time over this.

I want to know why are using pick(match(10,....) havent got this 10?

Please explain!

shwetagupta
Partner - Creator II
Partner - Creator II
Author

Yeah I am talking about green part. I am confused over Last three lines of your reply

Class],'MEDIUM','MASS'),'1','1'),'NA')))))))))*

Count(Distinct VisNormQty)

,VisibilityNorm, [Vis_Product Class]), ', ')


So If I am getting you correct you are aggregation over three parameters

1. Count(Distinct VisNormQty), // replaced with my field later

2.  VisibilityNorm

3. Vis_product Class

but when I am using your stated expression it is giving me error. Because of *Count(Distinct VisNormQty) this.


Secondly
why have you used ',' at the end


sunny_talwar

I am only aggregating over two dimensions.... VisibilityNorm and Vis_product Class.... In fact there is nothing like aggregating over an expression...

This is what you shared, right?

If(Match(VisibilityNorm,'DGATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),

if(Match(VisibilityNorm,'DGBNext45'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),

If(Match(VisibilityNorm,'DGRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','2'),

If(Match(VisibilityNorm,'SATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'1','1','2'),

If(Match(VisibilityNorm,'SBNext45'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),

If(Match(VisibilityNorm,'SRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),

If(Match(VisibilityNorm,'BATop8'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM'),'1','1'),

If(Match(VisibilityNorm,'BBNext45'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),

If(Match(VisibilityNorm,'BRest'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),'NA')))))))))*

Count({<AccountChannel={'GT'}, Max_Vis_Flag={'1'}>}Distinct dealer_code_c)

Since, the sample data you shared did not include AccountChannel, Max_Vis_Flag and dealer_code_c... I changed the expression to this

Count(Distinct VisNormQty)

All you need is this

Sum(Aggr(

If(Match(VisibilityNorm,'DGATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),

if(Match(VisibilityNorm,'DGBNext45'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'2','2','2'),

If(Match(VisibilityNorm,'DGRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','2'),

If(Match(VisibilityNorm,'SATop8'), Pick(Match([Vis_Product Class],'SUPER PREMIUM','PREMIUM','MEDIUM'),'1','1','2'),

If(Match(VisibilityNorm,'SBNext45'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),

If(Match(VisibilityNorm,'SRest'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM','MASS'),'1','1','1'),

If(Match(VisibilityNorm,'BATop8'), Pick(Match([Vis_Product Class],'PREMIUM','MEDIUM'),'1','1'),

If(Match(VisibilityNorm,'BBNext45'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),

If(Match(VisibilityNorm,'BRest'), Pick(Match([Vis_Product Class],'MEDIUM','MASS'),'1','1'),'NA')))))))))*

Count({<AccountChannel={'GT'}, Max_Vis_Flag={'1'}>}Distinct dealer_code_c)


,VisibilityNorm, [Vis_Product Class]))

The comma at the end was by mistake... I copy and pasted the Concat expression and replaced Concat with Sum.... Concat used comma as a delimiter, but sum doesn't need it. It was my bad

shwetagupta
Partner - Creator II
Partner - Creator II
Author

Thanks for so much of patience.

Got you now!
I have used this approach as well but output is not coming correct. It simply multiply normqty with dealer count without taking care of VisibilityNorm

just clear one point, is it possible/feasible to plot my above scenario on a single dimension i.e vis_product class?

now I am completely confused over this.

sunny_talwar

I think it is def. possible but I am just not sure what you want your final output to look like when you have multiple fields.... I am going to give you an example, and you tell me what the final output needed is...

When you have two dimensions

A             B          Measure

ABC     DEF               10

ABC     KLM               20

Not let's say you want to keep only A here....

A           Measure

ABC          What should come here? 30 (Sum)? 15 (Avg)? 10 (Min)? 20 (Max)? or something else?

pradosh_thakur
Master II
Master II

10 is just a dummy number. It can be replaced by anything other than 1 or 2 that is returned by your expression

alt returns 10 if yourexpression is null

so simplifying it

match(10,alt(yourexpression,10)) will return 0 when your expression is not null as it will return 1 or 2 . I have added + 1 so it will pick the first expression inside picki that is your_expression * count_expression.

secondly when there is a null returned, which will be the case when no filter is selected in VisibilityNorm. alt will return 10. and match will match it and return 1. +1 will return 2. so pick will pick the second expression. . you can see what i tried there was to incliude all the visibilty norm ion the set expression ..


Sunny rightly asked what you want to do when there is no filter selected sum? min? avg? max ?


you have to replace in my second expression or else you can try stalwar1 solution as well which is less lengthy and looks better.

Learning never stops.