topic Pick match for multiple If in QlikView

Pick match for multiple If

prasadcm — Wed, 02 Mar 2016 07:15:40 GMT

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

Re: Pick match for multiple If

Gysbert_Wassenaar — Wed, 02 Mar 2016 16:10:06 GMT

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.

Re: Pick match for multiple If

petter — Wed, 02 Mar 2016 20:37:50 GMT

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.