16 Replies Latest reply: Jan 3, 2018 10:27 PM by pradosh thakur RSS

    If else and Pick match over two dimensional expression

    shweta gupta

      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


        • Re: If else and Pick match over two dimensional expression
          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)

            • Re: If else and Pick match over two dimensional expression
              shweta gupta

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

                • Re: If else and Pick match over two dimensional expression
                  pradosh thakur

                  so when nothing is selected you want to see the total

                  try this or  sunny may help you more.

                  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))

              • Re: If else and Pick match over two dimensional expression
                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?

                  • Re: If else and Pick match over two dimensional expression
                    shweta gupta

                    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.

                      • Re: If else and Pick match over two dimensional expression
                        Sunny Talwar

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

                         

                        Capture.PNG

                         

                        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....

                          • Re: If else and Pick match over two dimensional expression
                            shweta gupta

                            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.

                              • Re: If else and Pick match over two dimensional expression
                                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?

                                  • Re: If else and Pick match over two dimensional expression
                                    shweta gupta

                                    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


                                      • Re: If else and Pick match over two dimensional expression
                                        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

                                          • Re: If else and Pick match over two dimensional expression
                                            shweta gupta

                                            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.

                                              • Re: If else and Pick match over two dimensional expression
                                                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?