Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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!

1 Solution

Accepted Solutions
Highlighted

Re: How to combine two fields (not concatenate)?

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

View solution in original post

13 Replies
Highlighted

Re: How to combine two fields (not concatenate)?

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 ...;

Highlighted
Creator II
Creator II

Re: How to combine two fields (not concatenate)?

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

ID_1Total_1ID_2Total_2
ABC10XYZ50
ABC10TUV30

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_combinedTotal_combined
ABC10
XYZ50
TUV30
Highlighted

Re: How to combine two fields (not concatenate)?

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

Highlighted
Specialist III
Specialist III

Re: How to combine two fields (not concatenate)?

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;

Highlighted
Master II
Master II

Re: How to combine two fields (not concatenate)?

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*') ;

Learning never stops.
Highlighted
Master II
Master II

Re: How to combine two fields (not concatenate)?

Hi Mark

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

regards

Pradosh

Learning never stops.
Highlighted
Creator II
Creator II

Re: How to combine two fields (not concatenate)?

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.

Highlighted

Re: How to combine two fields (not concatenate)?

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

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Creator II
Creator II

Re: How to combine two fields (not concatenate)?

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.