Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
Specialist II
Specialist II

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

-Sundar

Highlighted
Not applicable

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

Highlighted
Partner
Partner

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.

Highlighted
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

Highlighted
Not applicable

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

Highlighted
Not applicable

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

Highlighted
Partner
Partner

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

Highlighted
Not applicable

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!

Highlighted
MVP & Luminary
MVP & Luminary

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.