Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
markramiro
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
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

View solution in original post

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

markramiro
Creator II
Creator II
Author

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
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
Specialist III
Specialist III

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
Master II
Master II

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.
pradosh_thakur
Master II
Master II

Hi Mark

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

regards

Pradosh

Learning never stops.
markramiro
Creator II
Creator II
Author

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,

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

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.