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

How to combine two fields (not concatenate)?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

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

sunny_talwar — Fri, 29 Dec 2017 18:03:12 GMT

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

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

Anonymous — Fri, 29 Dec 2017 18:32:07 GMT

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

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

sunny_talwar — Fri, 29 Dec 2017 18:37:02 GMT

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

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

mdmukramali — Fri, 29 Dec 2017 18:41:39 GMT

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;

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

pradosh_thakur — Fri, 29 Dec 2017 20:52:34 GMT

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

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

pradosh_thakur — Tue, 02 Jan 2018 05:53:06 GMT

Hi Mark

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

regards

Pradosh

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

Anonymous — Tue, 02 Jan 2018 07:26:47 GMT

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.

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

PrashantSangle — Tue, 02 Jan 2018 07:47:20 GMT

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

Regards,

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

Anonymous — Tue, 02 Jan 2018 08:12:15 GMT

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.

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

sunny_talwar — Tue, 02 Jan 2018 09:42:25 GMT

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;

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

Anonymous — Tue, 02 Jan 2018 12:09:47 GMT

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.

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

sunny_talwar — Tue, 02 Jan 2018 12:20:09 GMT

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;

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

Anonymous — Wed, 03 Jan 2018 14:13:02 GMT

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.