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: 
Not applicable

Multiple if statements in Edit script

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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 ..;

View solution in original post

16 Replies
sundarakumar
Specialist II
Specialist II

You can use multiple if statement in script , Please share the sample data and calculation required for more calculation.

-Sundar

Not applicable
Author

Hi Sundar,

Appreciate your help on this. Please find below table for the sample data.

Customer typeBook TypeAction TypeGrossNetNew GrossNew Net
DirectStandardFixed84017306408095
DirectStandardFlex77235524006736
DirectStandardFixed89192616148436
DirectStandardFlex512481962505
DirectStandardNon Flex66875213630178
DirectContractFixed32963356875108
DirectContractFixed3987344985110
DirectContractFixed40331706006432
DirectContractFixed19876203911915
DirectContractFixed47364357466208
IndirectStandardFixed68208001949899
IndirectStandardFixed55882688412537
IndirectStandardFixed40485152215828
IndirectStandardFixed51686939337533
IndirectStandardFlex42874791575258
IndirectStandardFlex41193805777301
IndirectStandardFlex8851431176198
IndirectStandardFlex68842238016896
IndirectStandardFlex44271864740524
IndirectStandardFlex35974843192789
IndirectStandardFlex31534939352062
IndirectContractFlex24771675625166
IndirectContractFlex37060352787424
IndirectContractFlex94723757376406
IndirectContractFlex13520829350363
IndirectContractFlex98051259818830

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

brijesh1991
Partner - Specialist
Partner - Specialist

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.

tresesco
MVP
MVP

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 ..;

Not applicable
Author

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

Not applicable
Author

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.......;

martinpohl
Partner - Master
Partner - Master

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

Not applicable
Author

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!

jagan
Luminary Alumni
Luminary Alumni

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.