creating a case using 2 different columns

TIBCO Spotfire Community » TIBCO Spotfire » TIBCO Spotfire Clients » creating a case using 2 different columns

creating a case using 2 different columns

Last post Tue, Jul 27 2010 12:02 PM by EranS. 3 replies.

Page 1 of 1 (4 items)
	Sort Posts: Previous Next

Mon, Jul 26 2010 5:49 PM

cindys
Joined on Mon, Jul 26 2010
Posts 4

creating a case using 2 different columns

Reply Contact

i need to create a case that needs 2 different columns to create the then statement. for example if gen = 801 and sub = 101 then "Category 1). I know how to create a case for one or the other, but not both.

Tue, Jul 27 2010 2:59 AM In reply to

EranS
Joined on Mon, Oct 12 2009
Israel
Posts 355

Re: creating a case using 2 different columns

Reply Contact

Hi Cindy,

You defined the case statement exactly as it should be. The syntax is the only gap.

Try:

CASE

WHEN [gen] = 801 AND [sub] = 101 THEN "Category 1"

WHEN [gen] > 801 AND [sub] < 101 THEN "Category 2"

ELSE "NO CATEGORY"

END

--- The sytax above is for multi logic

There is also one for coulmn value listings: case [gen] when 101 then 1 when 102 then 2 else 3 end

Best Regards,

Eran

Tue, Jul 27 2010 10:34 AM In reply to

cindys
Joined on Mon, Jul 26 2010
Posts 4

Re: creating a case using 2 different columns

Reply Contact

Thanks Eran, Now how do I do an include since I have a range of subs that are random. i.e. 101, 201, 401, 502. I tried to use "in" and it gave me an error.

Tue, Jul 27 2010 12:02 PM In reply to

EranS
Joined on Mon, Oct 12 2009
Israel
Posts 355

Re: creating a case using 2 different columns

Reply Contact

Hi,

Spotfire does not support ansi SQL commands,

Try:

CASE

WHEN find( string([gen]) , "801,834,888" ) >0 AND [sub] = 101 THEN "Category 1"

....

ELSE "NO CATEGORY"

END

The list is in the string within the find function. I added the seperartor to avioid any un-intended combinations.

If the find function finds a match the returned value will be greater or equal to 1 . No match returns 0.

I used string function for the gen column assuming it is originally an integer otherwise it is not needed.

Regards,

Eran

Page 1 of 1 (4 items)

Spotfire's interactive information visualization and analytic solutions give users a remarkable experience for quickly and easily querying data and reporting results for superior business intelligence. From portfolio management and customer retention programs to key processes such as CRM, marketing, research, bioinformatics, yield and asset management and design for manufacturing, enterprises around the world rely on Spotfire's business analytics software to improve operational performance.