Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to combine two fields (not concatenate)?

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_1Total_1
ABC10
DEF20
GHI20
JKL5

ID_2Total_2
XYZ50
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_combinedTotal_combined
ABC10
DEF20
GHI20
JKL5
XYZ50
TUV30

Thanks for the help!

13 Replies
sunny_talwar

Is this the goal?

Capture.PNG

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
Not applicable
Author

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

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;


Capture.PNG

Anonymous
Not applicable
Author

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.