Is it possible to Union two or more two column values at Spotfire front end level?

[Kumud K]

Let say, from information link I loaded 10 different columns now want a calculated column which contains values of column1, column2, column3 similar to union function in sql, is that possible to do that?

[Fredrik Rosell]

Hello,

Could you please illustrate what you are trying to achieve - what the data looks like from the start and the intended end result? Is it a on-off operation or something that should happen automatically/dynamically?

 

Some basic solutions that may or not may work for your use case:

 

Example 1:

Example: if your Information Link returns Column1 and Column 2.

Column1,Column2
1,3
2,4

By union I assume you might want the end result to look like this 

MergedColumn

1
2
3
4

If you want to do that after importing the data into Spotfire (as you wrote "front end level", that might be what you need)  - not on the database side/as part of the information link , you could add an Unpivot transformation after importing the data into Spotfire. To quote the manual, ""Unpivot Data can be used to transform data from a short/wide format to a tall/skinny format"", which basically means taking multiple columns and "unioning" them into one. You can read more about that transformation here:

https://docs.tibco.com/pub/sfire-analyst/latest/doc/html/en-US/TIB_sfire-analyst_UsersGuide/index.htm#t=data%2Fdata_details_on_unpivot_data.htm

 

Example 2

If you want to transform the data in the Information Link itself, you could e.g. use the ability to Edit SQL to add the UNION:s there.   

SELECT
   U1."COLUMN1" AS "COLUMN1"
FROM
   "MyDB"."COLUMNS" U1
UNION
SELECT
   U1."COLUMN2" AS "COLUMN2"
FROM
   "MyDB"."COLUMNS" U1
WHERE
<conditions>

 

Note: for transforming data the other way in an Information Link - doing a pivot (i.e. tall/skinny to short/wide) - that is easier, as there is a build-in pivot operation available under the Conditioning tab on the Information Link.      

 

Best Regards

Fredrik