I have a following issue in my WEBI Report. For Example, Giftcard 1234 has actual card value as $100, its redeemed value $30, then I need to show balance due amount as $70. While getting data from backend, I am getting this information in two different transactions with different Card categories.
|Gift Card nbr||Card Catg||Gift card act value||Gift card redeem value||Balance Due|
|Transaction 1||1234||1 (Actual)||100||0||100|
|Transaction 2||1234||2 (Redeemed)||0||30||30|
Unfortunately, I do not have control on the backend data or on the semantic layer (Universe level). I have tried creating Variable with If statement using Card Catg, Gift card actual value and redeemed value but that didnt help.
Can you please help me out if there’s easy way to resolve the issue.