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: 
prasadcm
Creator II
Creator II

Pick match for multiple If

Hi,

i have an expression like following

=if(([Flip by Source No_]=1 AND [Flip by Business Line]=0 AND [Flip by Company]=0 AND [Flip by IC Partner]=0 AND [Flip by VAT Bus_ Posting Group]=0), //condn 1 source NO

                     if([Balance Flip Sign]=1,

                           if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[Source No_])<0, [Flipping Account No_],[GL account code]),

                           //else

                         if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[Source No_])>0, [Flipping Account No_],[GL account code])

                       ),

                       //else condn 2

                     if(([Flip by Source No_]=0 AND [Flip by Business Line]=1 AND [Flip by Company]=0 AND [Flip by IC Partner]=0 AND [Flip by VAT Bus_ Posting Group]=0), //condn 2 Business Line

                     if([Balance Flip Sign]=1,

                           if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[Business Line])<0, [Flipping Account No_],[GL account code]),

                           //else

                         if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[Business Line])>0, [Flipping Account No_],[GL account code])

                       ),

                      //else condn 3

                      if(([Flip by Source No_]=0 AND [Flip by Business Line]=0 AND [Flip by Company]=1 AND [Flip by IC Partner]=0 AND [Flip by VAT Bus_ Posting Group]=0), //condn 3 Company

                           if([Balance Flip Sign]=1,

                                  if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[Entity Name])<0, [Flipping Account No_],[GL account code]),

                                  //else

                            if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[Entity Name])>0, [Flipping Account No_],[GL account code])

                              ),

                           //else condn 4

                        if(([Flip by Source No_]=0 AND [Flip by Business Line]=0 AND [Flip by Company]=0 AND [Flip by IC Partner]=1 AND [Flip by VAT Bus_ Posting Group]=0), //condn 4 IC Partner

                           if([Balance Flip Sign]=1,

                                  if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[IC Partner Code])<0, [Flipping Account No_],[GL account code]),

                                  //else

                            if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[IC Partner Code])>0, [Flipping Account No_],[GL account code])

                              ),

                     //else condn 5

                      if(([Flip by Source No_]=0 AND [Flip by Business Line]=0 AND [Flip by Company]=0 AND [Flip by IC Partner]=0 AND [Flip by VAT Bus_ Posting Group]=1), //condn 5 VAT BUS

                           if([Balance Flip Sign]=1,

                                  if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[VAT Bus_ Posting Group])<0, [Flipping Account No_],[GL account code]),

                                  //else

                            if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[VAT Bus_ Posting Group])>0, [Flipping Account No_],[GL account code])

                              ),)))))

How am I supposed to put these in pick match function?

Regards,

Prasad

2 Replies
Gysbert_Wassenaar

I don't think you can easily translate this to a pick-match combination. The cure will probably be worse than the disease. Perhaps it's possible to test for the conditions in the script and add a flag field based on that so you can get rid of the outer if statements.


talk is cheap, supply exceeds demand
petter
Partner - Champion III
Partner - Champion III

Gysbert might be right - but if you have to implement it by Pick() and Match() this seems to be easier to read and maintain - at least in my eyes:

Pick(

  Pick(

  Match(

  [Flip by Source No_] & [Flip by Business Line] & [Flip by Company] & [Flip by IC Partner] & [Flip by VAT Bus_ Posting Group] & (-([Balance Flip Sign]=1)),

  '100001',

  '100000',

  '010001',

  '010000',

  '001001',

  '001000',

  '000101',

  '000100',

  '000011'

  '000010'

  )

  ,

  2+(Aggr( NODISTINCT Sum(DISTINCT [Addition Currency Amount]) , [GL account code] , [Source No_] ) < 0)

  2+(Aggr( NODISTINCT Sum(DISTINCT [Addition Currency Amount]) , [GL account code] , [Source No_] ) > 0)

  2+(Aggr( NODISTINCT Sum(DISTINCT [Addition Currency Amount]) , [GL account code] , [Business Line] ) < 0)

  2+(Aggr( NODISTINCT Sum(DISTINCT [Addition Currency Amount]) , [GL account code] , [Business Line] ) > 0)

  2+(Aggr( NODISTINCT Sum(DISTINCT [Addition Currency Amount]) , [GL account code] , [Entity Name] ) < 0)

  2+(Aggr( NODISTINCT Sum(DISTINCT [Addition Currency Amount]) , [GL account code] , [Entity Name] ) > 0)

  2+(Aggr( NODISTINCT Sum(DISTINCT [Addition Currency Amount]) , [GL account code] , [IC Partner Code] ) < 0)

  2+(Aggr( NODISTINCT Sum(DISTINCT [Addition Currency Amount]) , [GL account code] , [IC Partner Code] ) > 0)

  2+(Aggr( NODISTINCT Sum(DISTINCT [Addition Currency Amount]) , [GL account code] , [VAT Bus_ Posting Group] ) < 0)

  2+(Aggr( NODISTINCT Sum(DISTINCT [Addition Currency Amount]) , [GL account code] , [VAT Bus_ Posting Group] ) > 0)

  ),

  [Flipping Account No_]

  ,[GL account code]

)

It seems like at binary pattern matching and for all I know it might be possible to simplify it much further.

I would think that making the aggregations in the load script by a rather simple LOAD Sum(..), ..... RESIDENT FROM basetable GROUP BY and so forth could let the data model precalculate it for you. So all the heavy lifting would not be in the UI at run-time of the app but during reload time - and then it shouldnt be very heavy anyway.