How to combine two fields (not concatenate)?

[Anonymous]

Hi,

How can I combine the values of two fields into just one field? Note I am NOT trying to concatenate them. I have searched the forums and most discussions around combining/merging are about concatenating the values, which is different from what I am trying to achieve. I have two fields from two different tables (total of four fields). However for this chart, I need the Dimension to be a field containing DISTINCT values from both fields, while the Expression would be their corresponding values.

Example, I have two loaded tables:

ID_1	Total_1
ABC	10
DEF	20
GHI	20
JKL	5

ID_2	Total_2
XYZ	50
TUV	30

I need to have combined fields for ID_1 and ID_2 (called "ID_combined") as well as Total_1 and Total_2 (called "Total_combined") that I can utilize for a chart:

ID_combined	Total_combined
ABC	10
DEF	20
GHI	20
JKL	5
XYZ	50
TUV	30

Thanks for the help!

[sunny_talwar]

May be you can do something like this?

Table:

LOAD ID_1,

     Total_1,

     ID_1 as  ID_combined,

     Total_1 as Total_combined

FROM ...;

Concatenate(Table)

LOAD ID_2,

     Total_2,

     ID_2 as  ID_combined,

     Total_2 as Total_combined

FROM ...;

[Anonymous]

I realize my situation is slightly different than what I initially described. My loaded table looks something like this:

ID_1	Total_1	ID_2	Total_2
ABC	10	XYZ	50
ABC	10	TUV	30

There is a one to many relationship between ID_1 and ID_2 but they are both technically ID's.

I then need to combine ID_1 and ID_2 into a single field called "ID_combined" with their corresponding "Total" values (i.e. Total_1 for ID_1 and Total_2 for ID_2) below a new field, "Total_combined" that I can utilize for a chart:

ID_combined	Total_combined
ABC	10
XYZ	50
TUV	30

[sunny_talwar]

May be this in that case

Table:

LOAD ID_1,

     Total_1,

     ID_2,

     Total_2

FROM ....

NewTable:

LOAD DISTINCT

     ID_1 as ID_combined,

     Total_1 as Total_combined

Resident Table;

Concatenate (NewTable)

LOAD DISTINCT

     ID_2 as ID_combined,

     Total_2 as Total_combined

Resident Table;

This will create two un-linked table... I am not sure if that is what you want or not

[mdmukramali]

Load

Distinct              

ID_1 as ID_Combined,

Total_1 as Total_Combined

From Table;

Concatenate

Load

Distinct

ID_2 as ID_Combined,

Total_2 as Total_Combined

From Table;

[pradosh_thakur]

what about

LOAD DISTINCT ID_1 AS ID_Combined, Total_1 as Total_Cobmined INLINE [
    ID_1, Total_1, ID_2, Total_2
    ABC, 10, XYZ, 50
    ABC, 10, TUV, 30
]  (embedded labels, filters(
Unwrap(Col, Pos(Top, 3))))
WHERE NOT WildMatch(ID_1,'ID*') ;

[pradosh_thakur]

Hi Mark

did you get your answer ? If yes please close the thread or else feel free to ask.

regards

Pradosh

[Anonymous]

I have added a sample file to further illustrate exactly how the tables are laid out. The script you have given works except that as you have said they are not linked. I am trying to link them. Appreciate the help.

[PrashantSangle]

can you post your required output w.r.t input??

Regards,

[Anonymous]

Require output is also on the attached file. I need a single field that combines the two ID fields. And the new table should be linked to the existing tables.