If else and Pick match over two dimensional expression

[shwetagupta]

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

[pradosh_thakur]

try this may be

alt(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'))))))))),1)*

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

[sunny_talwar]

May be you need to add VisibilityNorm as one of your dimension here? Because there are multiple VisibilityNorm, the chart doesn't know whoes value it needs to show. Or you may be want to show Min/Max/Avg/Sum from them?

[shwetagupta]

Thanks a ton Pradosh, but this helped me partially!!

[shwetagupta]

Yeah Sunny I am seeking for a way to take out data corresponding Vis_Product Class in my final output though I am getting your point of plotting other dimension. And actually when i plot other field in my output table it result me correct. but need to script or write expression in a way that I will be able to take information corrresponding to mentioned field.

Or I expect that this information can be plot over two-dimensions only.

[pradosh_thakur]

so when nothing is selected you want to see the total

try this or  sunny may help you more.

ps: code edited since the original post

pick(match(10,

alt(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'))))))))),10))+1,

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

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(total <VisibilityNorm> {<AccountChannel={'GT'}, Max_Vis_Flag={'1'}>}Distinct dealer_code_c))

[shwetagupta]

Can you please explain your expression once!

[pradosh_thakur]

The above expression will return the corresponding value when VisibilityNorm is selected if not then it will show the total for Vis_Product Class for all the VisibilityNorm. at least that is what i was trying .

if the above is not showing the desired result than try replacing

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

with

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

[sunny_talwar]

You see how there are multiple values when we use your expression (modified version of your expression)

Which one should Qlik be showing as the value? Sum them? Avg them? Find Min or Max? What exactly do you want to see as the output?

For Sum, you can do 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(Distinct VisNormQty)

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

Change Sum to whatever type of aggregation you wish to see....

[shwetagupta]

Hey Sunny,

i got your point, could you please help me in understanding below mentioned part of the expression. Though i have alreay use aggregation sum over visibility norm and vis_product class.

Count(Distinct VisNormQty),

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

But as expected it multiplies dealers with norm without considering the norm.
As much i have understood, I need to plot both the dimensions in the table to get the correct data since we are putting condition using two different dimensions then how can i plot data corresponding to single dimension.

I hope i am making some sense, else suggest me some other way to get out of this problem.