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]

Is this the goal?

Script

MainTable:

LOAD * INLINE [

    ID_1, Total

    ABC, 50

    DEF, 20

    GHI, 10

    XYZ, 30

];

//This script above is fixed and cannot be modified

RelatedIDTable:

LOAD * INLINE [

    ID_1, ID_2

    ABC, DEF

    ABC, XYZ

    DEF, ABC

    DEF, GHI

    GHI, DEF

    XYZ, ABC

];

LinkedTable:

LOAD ID_1 as ID_Combined,

ID_2 as ID_1

Resident RelatedIDTable;

Concatenate (LinkedTable)

LOAD DISTINCT ID_1 as ID_Combined,

ID_1 as ID_1

Resident RelatedIDTable;

[Anonymous]

Hi Sunny,

Looks good except it's the other way around. The selection would be done on ID_1. So for example if I select ID_1=ABC then ID_Combined should show {ABC, DEF, XYZ} and Total_Combined should show {50, 20, 30} respectively.

[sunny_talwar · Accepted solution]

Confused as to what the goal is, but may be this

MainTable:

LOAD * INLINE [

    ID_1, Total

    ABC, 50

    DEF, 20

    GHI, 10

    XYZ, 30

];

//This script above is fixed and cannot be modified

RelatedIDTable:

LOAD * INLINE [

    ID_1, ID_Combined

    ABC, DEF

    ABC, XYZ

    DEF, ABC

    DEF, GHI

    GHI, DEF

    XYZ, ABC

];

Concatenate (RelatedIDTable)

LOAD DISTINCT ID_1,

ID_1 as ID_Combined

Resident RelatedIDTable;

Left Join (RelatedIDTable)

LOAD ID_1 as ID_Combined,

Total as Total_Combined

Resident MainTable;

[Anonymous]

Thanks! This was almost what I needed. Your solution required modifying a portion of the original script that I could not touch. However I was able to adopt the same methodology you implemented using concatenate and left joins to get exactly what I was trying to achieve.