Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.