Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 2 columns Gross revenue and net revenue and 3rd column customer type where some charater values are there. I need to create new gross and new net columns based on some condition using customer type. How can i use multiple if statement in edit script for this? Any help appreciated
try like:
Load
Gross,
Net,
[Customer type],
If([Custmer type] = 'Indirect', Gross, If([Action type]='Standard' OR [Action type]='Fixed', Gross )) AS [New Gross],
If([Custmer type] = 'Indirect', Net, If([Action type]='Standard' OR [Action type]='Fixed', Net)) AS [New Net]
From ..;
You can use multiple if statement in script , Please share the sample data and calculation required for more calculation.
-Sundar
Hi Sundar,
Appreciate your help on this. Please find below table for the sample data.
Customer type | Book Type | Action Type | Gross | Net | New Gross | New Net |
Direct | Standard | Fixed | 8401730 | 6408095 | ||
Direct | Standard | Flex | 7723552 | 4006736 | ||
Direct | Standard | Fixed | 8919261 | 6148436 | ||
Direct | Standard | Flex | 512481 | 962505 | ||
Direct | Standard | Non Flex | 6687521 | 3630178 | ||
Direct | Contract | Fixed | 3296335 | 6875108 | ||
Direct | Contract | Fixed | 3987344 | 985110 | ||
Direct | Contract | Fixed | 4033170 | 6006432 | ||
Direct | Contract | Fixed | 1987620 | 3911915 | ||
Direct | Contract | Fixed | 4736435 | 7466208 | ||
Indirect | Standard | Fixed | 6820800 | 1949899 | ||
Indirect | Standard | Fixed | 5588268 | 8412537 | ||
Indirect | Standard | Fixed | 4048515 | 2215828 | ||
Indirect | Standard | Fixed | 5168693 | 9337533 | ||
Indirect | Standard | Flex | 4287479 | 1575258 | ||
Indirect | Standard | Flex | 4119380 | 5777301 | ||
Indirect | Standard | Flex | 885143 | 1176198 | ||
Indirect | Standard | Flex | 6884223 | 8016896 | ||
Indirect | Standard | Flex | 4427186 | 4740524 | ||
Indirect | Standard | Flex | 3597484 | 3192789 | ||
Indirect | Standard | Flex | 3153493 | 9352062 | ||
Indirect | Contract | Flex | 2477167 | 5625166 | ||
Indirect | Contract | Flex | 3706035 | 2787424 | ||
Indirect | Contract | Flex | 9472375 | 7376406 | ||
Indirect | Contract | Flex | 1352082 | 9350363 | ||
Indirect | Contract | Flex | 9805125 | 9818830 |
Say now, i want to create a column called "New Gross" where for all "Indirect" Customers i want the Gross to be copied as it is. But for "Direct" Customers Gross needs to be copied only when Book type is "Standard" and Action type is "Fixed". How can i write a statement in Edit script for this
if(Condition1, Field/Expression,
if(Condition2, Field2/Expression,
if(Condition1, Field3/Expression)))
Where your condition may be combination of AND , OR or a single expression.
try like:
Load
Gross,
Net,
[Customer type],
If([Custmer type] = 'Indirect', Gross, If([Action type]='Standard' OR [Action type]='Fixed', Gross )) AS [New Gross],
If([Custmer type] = 'Indirect', Net, If([Action type]='Standard' OR [Action type]='Fixed', Net)) AS [New Net]
From ..;
Hi Malik,
If(CustomerType='Indirect' , Gross,
if(CustomerType='Direct' and BookType='Standard' and ActionType='Fixed', Gross)) As NewGross.
I have given multiple condition based.
Regards,
Santhosh G
Try This
Load *,
if(Customer type='Indirect',Gross
,if(Customer type='Direct' and [Book Type]='Standard' and [Action Type]='Fixed',Gross)) as New Gross,
if(Customer type='Indirect',Net
,if(Customer type='Direct' and [Book Type]='Standard' and [Action Type]='Fixed',Net)) as New Net,
From.......;
For your example:
load
[Customer type],
[Book Type],
[Action Type],
Gross,
Net,,
if([Custer Type]='Indirect' or ([Book Type]='Standard' and [Action Type]='Fixed'),Gross,0,) as [New Gross]
from Data;
Regards
Hi,
You can try the below:
Temp:
Load *,
Gross,
Net,
[Customer type],
If([Custmer type] = 'Indirect', Gross,
If([Book type]='Standard' OR [Action type]='Fixed', Gross )) AS [New Gross],
If([Custmer type] = 'Indirect', Net,
If([Book type]='Standard' OR [Action type]='Fixed', Net)) AS [New Net]
From source xls;
As of now, I have assumed that you want the same conditions for "New Gross" and "New Net".
Hope this helps!
Hi,
Try like this
LOAD
*,
If( [Customer type] = 'Indirect', Gross,
If([Customer type] = 'Direct' AND [Book Type] = 'Standard' AND [Action Type] = 'Fixed', Gross, 0))
Regards,
Jagan.