topic Re: Urgent, if else in QlikView

Urgent, if else

n1ef5ng1 — Mon, 27 May 2013 00:46:42 GMT

I have a column that stated Indonesia Region with another column to all countries including indonesia.

Indonesia - Brunei

Indonesia - Malaysia

Indonesia - China

Indonesia - Indonesia

Indonesia - Singapore

Indonesia - America

Qn is I need two set of formula to derive the data in a column value

One formula for Indonesia - to countries excluding Indonesia

Another formula for Indonesia to Indonesia

Is there any way i can do it?

Re: Urgent, if else

Sokkorn — Mon, 27 May 2013 01:46:30 GMT

Hi n1ef5ng1,

Here you are:

1. =Sum({$<[Indonesia Region] -={'Indonesia - Indonesia'}>} Sales)

2. =Sum({$<[Indonesia Region] ={'Indonesia - Indonesia'}>} Sales)

Regards,

Sokkorn

Re: Urgent, if else

n1ef5ng1 — Mon, 27 May 2013 01:50:31 GMT

is that under two expression or one expression

Re: Urgent, if else

Sokkorn — Mon, 27 May 2013 01:51:38 GMT

Hi,

Two set analyisis.

Rgds,

Sokkorn

Re: Urgent, if else

n1ef5ng1 — Mon, 27 May 2013 01:56:10 GMT

This is my code to get Indonesia to Others

=Sum({<

Class_Name = {'Laden'}

,Status = {'T'}

,Unlocode_Place_Of_Loading = {'ID*'}

>} Sales)

This is my code to get Indonesia to Indonesia

=Sum({<

Class_Name = {'Laden'}

,Status = {'T'}

,Unlocode_Place_Of_Loading = {'ID*'}

,Unlocode_Place_of_Discharge = {'ID*'}

>} Sales)

your help is deeply appreciated

Re: Urgent, if else

Sokkorn — Mon, 27 May 2013 02:07:07 GMT

Hi n1ef5ng1,

Can you share your app with sample data?

Regards,

Sokkorn

Re: Urgent, if else

n1ef5ng1 — Mon, 27 May 2013 02:09:47 GMT

i can't cause i am using personal edition.

How about a simple guaidance?

Tried using if else statement but it does not work.

The problem is to fit in two formula in different condition under one expression

Re: Urgent, if else

Mon, 27 May 2013 04:08:02 GMT

Define a flag for combination Indonesia - Indonesia and Indonesia - Others, then use it anywhere in the appliction, this will solve ur problem.

Re: Urgent, if else

sivarajs — Mon, 27 May 2013 04:30:00 GMT

Try using this expression

if(trim(SubField(Tmp,'-',1))=trim(SubField(Tmp,'-',2)),'Matching','Not Matching')

Re: Urgent, if else

n1ef5ng1 — Mon, 27 May 2013 04:47:18 GMT

hwo do you define a flag, something new to me

Re: Urgent, if else

n1ef5ng1 — Mon, 27 May 2013 04:47:37 GMT

whats is TMP? and what is 1, 2

Re: Urgent, if else

Mon, 27 May 2013 04:51:50 GMT

Hi ,

As you mentioned you needed to define 2 formulas under one Expression.

Just wanted to check what is the Condition under which you wanted to fit these 2 formulas?.

Means is there is any dimension w.r.t that you want to use this expression.

Regards

Yusuf

Re: Urgent, if else

sivarajs — Mon, 27 May 2013 05:00:08 GMT

Tmp is your field name and subfield is used to get the specific string

ex in your case

Indonesia - Indonesia

Indonesia - Malaysia

Indonesia - China

Indonesia - Indonesia

Indonesia - Singapore

each string has '-' as common subfield(fieldname,'-') will seperate each sun string like Indonesia,Singapore etc

1 represents string before your delimiter '-' and vice versa

Re: Urgent, if else

n1ef5ng1 — Mon, 27 May 2013 05:00:50 GMT

As attached. This table ID -> TSHIP refers to From ID(Indonesia) to _______

As you can see 10 PH means from ID to PH, ID to TH etc etc

However when it comes to ID to ID, i need a different sum expression

These are my formulas to derive

From ID to others

=Sum({<

Status = {'TSHIP'}
,Unlocode_Place_Of_Loading = {'ID*'}
>} Sales)

From ID to ID

=Sum({<

Status = {'TSHIP'}
,Unlocode_Place_Of_Loading = {'ID*'}

,Unlocode_Place_Of_Discharge = {'ID*}
>} Sales)

Re: Urgent, if else

Mon, 27 May 2013 05:06:57 GMT

If(Country='Indonesis - Indonesia','A','B') as CountryFlag

nw u gt 2 combinations in the countryflag column, one is Ind - Ind and Ind-OtherCountry

use it for ur calculations...

Re: Urgent, if else

n1ef5ng1 — Mon, 27 May 2013 05:14:55 GMT

=if

(

Country_Code = 'ID',

Sum(Derived_Status = 'TSHIP' and Container_Class_Name = 'Laden' and Unlocode_Port_Of_Loading = 'ID*' and Unlocode_Port_Of_Discharge = 'ID*')

,Sum(Derived_Status = 'TSHIP' and Container_Class_Name = 'Laden' and Unlocode_Port_Of_Loading = 'ID*')

)

does this works? what i get is OUt of object memory

Re: Urgent, if else

Mon, 27 May 2013 06:30:27 GMT

Hi,

please try to use set analysis in the expression mentioned by you.

=if

(

Country_Code = 'ID',

Sum({< Derived_Status = 'TSHIP' and Container_Class_Name = 'Laden' and Unlocode_Port_Of_Loading = 'ID*' and Unlocode_Port_Of_Discharge = 'ID*' >}Sales )

,Sum({<Derived_Status = 'TSHIP' and Container_Class_Name = 'Laden' and Unlocode_Port_Of_Loading = 'ID*')

>}Sales)

//Yusuf

Re: Urgent, if else

n1ef5ng1 — Mon, 27 May 2013 06:37:27 GMT

Hi i tried your code but there is an error in set modifier expression,

>}TEU

} is the error

Re: Urgent, if else

er_mohit — Mon, 27 May 2013 06:38:44 GMT

Try this

=if

(

Country_Code = 'ID',

Sum({< Derived_Status = {'TSHIP'}* Container_Class_Name = {'Laden'} * Unlocode_Port_Of_Loading ={ 'ID*'} * Unlocode_Port_Of_Discharge = {'ID*' }>}Sales ),

Sum({<Derived_Status = {'TSHIP'} *Container_Class_Name = {'Laden'} * Unlocode_Port_Of_Loading = {'ID*'}

>}Sales)

hope it helps

Re: Urgent, if else

n1ef5ng1 — Mon, 27 May 2013 06:47:28 GMT

tried your code but there is an error in set modifier expression,

>}TEU

} is the error