topic Re: Multiple if statements in Edit script in QlikView

Multiple if statements in Edit script

Thu, 09 Jan 2014 07:09:21 GMT

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

Re: Multiple if statements in Edit script

sundarakumar — Thu, 09 Jan 2014 07:15:15 GMT

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

-Sundar

Re: Multiple if statements in Edit script

Thu, 09 Jan 2014 07:23:23 GMT

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

Re: Multiple if statements in Edit script

brijesh1991 — Thu, 09 Jan 2014 07:28:40 GMT

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.

Re: Multiple if statements in Edit script

tresB — Thu, 09 Jan 2014 07:33:28 GMT

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

Re: Multiple if statements in Edit script

Thu, 09 Jan 2014 07:33:32 GMT

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

Re: Multiple if statements in Edit script

Thu, 09 Jan 2014 07:33:33 GMT

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

Re: Multiple if statements in Edit script

martinpohl — Thu, 09 Jan 2014 07:38:38 GMT

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

Re: Multiple if statements in Edit script

Thu, 09 Jan 2014 07:42:21 GMT

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!

Re: Multiple if statements in Edit script

jagan — Thu, 09 Jan 2014 07:44:54 GMT

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.

Re: Multiple if statements in Edit script

Thu, 09 Jan 2014 08:35:54 GMT

Hi Jagan,

I dont know why but for some reason this syntax is only copying for Gross for Indirect customers. For Direct customers and for some other conditions i have the gross is still showing 0.

Regards,

Mallik

Re: Multiple if statements in Edit script

Thu, 09 Jan 2014 08:36:22 GMT

Hi Shyamal,

I dont know why but for some reason this syntax is only copying for Gross for Indirect customers. For Direct customers and for some other conditions i have the gross is still showing 0.

Regards,

Mallik

Re: Multiple if statements in Edit script

Thu, 09 Jan 2014 08:36:42 GMT

Hi Nirmal,

I dont know why but for some reason this syntax is only copying for Gross for Indirect customers. For Direct customers and for some other conditions i have the gross is still showing 0.

Regards,

Mallik

Re: Multiple if statements in Edit script

Thu, 09 Jan 2014 08:37:06 GMT

Hi Tresesco,

I dont know why but for some reason this syntax is only copying for Gross for Indirect customers. For Direct customers and for some other conditions i have the gross is still showing 0.

Regards,

Mallik

Re: Multiple if statements in Edit script

tresB — Thu, 09 Jan 2014 08:48:50 GMT

Check these:

If the parenthesis are proper, or comparison string/field name is correct. Or, you might want to share a sample app.

Re: Multiple if statements in Edit script

Thu, 09 Jan 2014 10:13:23 GMT

You can check for the paranthesis or post a sample app, so it would be easier to pin point the issue.

Re: Multiple if statements in Edit script

Thu, 09 Jan 2014 11:59:41 GMT

YOU CAN ACHIEVE THIS IN TWO STEP-

during 1st load-

LOAD

If( [Customer type] = 'Indirect', 1,0) as GrossF1,

If([Custmer type] = 'direct' and [Book Type]='Standard' and [Action type]='Fixed', 1,0) as GrossF2

during 2nd load take resident of above table and try below code

load *,

if(GrossF1=1 or GrossF2=1,Gross) as new gross

hope this will help