topic Re: Merging multiple fields into one in QlikView

Merging multiple fields into one

Mon, 02 Nov 2015 11:31:07 GMT

Hello,

I have a table in which there is the same information in two different representations. The two fields can be ether both filled, or one of the fields is filled. I'd like to make one extra field, where the information is gathered.

One example:

brand	model	HP	kW
Ford	Mustang	304	224
Ford	Mustang GT350		392
BMW	M3 GTS	444
BMW	M5	560	412

Now I need a new field "Power". That field contains ether the kW value, the cenvrted HP value (HP/1,36) or both. The result would be:

brand	model	Power
Ford	Mustang	224
Ford	Mustang	223.529
Ford	Mustang GT350	392
BMW	M3 GTS	326.470
BMW	M5	412
BMW	M5	411.764

Thank you for your help, I'm a little bit lost here.

Thank you

Markus

Edit: I've changed the decimal seperator from , to . for clarification.

Re: Merging multiple fields into one

Anonymous — Mon, 02 Nov 2015 11:38:23 GMT

For me, both are two different fields.

If both are same then For Ford 304 & 224, should be same?

Re: Merging multiple fields into one

rubenmarin — Mon, 02 Nov 2015 11:40:51 GMT

Hello Markus, you can use '&' to concatenate strings:

LOAD brand, model, HP & If(Not IsNull(HP) and not IsNull(kW), ',') & kW; //Only adds the comma when both fields have values.

LOAD brand, model, HP, kW

FROM ...

Re: Merging multiple fields into one

Mon, 02 Nov 2015 11:57:39 GMT

Hi,

I'm sorry. I used the wrong decimal seperator, resulting in the wrong interpretation. I changed the decimal point from comma to point. (Here in Germany we write 100,00 instead of 100.00)

I don't want to concatenate the string representation of the two values. I want the values of the field HP and kW into a new field.

In the second table, the first row is the value of the kW field of the first table, first row.
In the second table, the second row is the value of HP field of the first table, first row. (diveded by 1.36 for conversation from horsepower to kilowatts)

I'm sorry I can't phrase it any better, my vocabulary is letting my down on this one.

Re: Merging multiple fields into one

cwolf — Mon, 02 Nov 2015 18:58:54 GMT

TABLE2:

Load

brand,

model,

kW as Power

resident TABLE1

where not IsNull(kW);

Load

brand,

model,

HP/1.36 as Power

resident TABLE1

where not IsNull(HP);

drop table TABEL1;

Re: Merging multiple fields into one

MarcoWedel — Mon, 02 Nov 2015 23:35:34 GMT

Hi,

one solution could be:

tabCars:
CrossTable ([Source Unit],PowerTemp,2)
LOAD * FROM [https://community.qlik.com/thread/189940] (html, codepage is 1252, embedded labels, table is @1);

Right Join (tabCars)
LOAD Distinct
    PowerTemp,
    [Source Unit],
    If([Source Unit]='HP',Num(PowerTemp/1.36,'0.###'),PowerTemp) as "Power [kW]"
Resident tabCars
Where Len(PowerTemp);

DROP Field PowerTemp;

hope this helps

regards

Marco

Re: Merging multiple fields into one

Tue, 03 Nov 2015 07:01:05 GMT

Thank you very much. Your solution works fine. But I'm going with MarcoWedel‌s solution. It better fits my needs.

Re: Merging multiple fields into one

Tue, 03 Nov 2015 07:01:58 GMT

Thank you very much. A quite elegant solution, didn't thought about using cross tables.