Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Merging multiple fields into one

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:

brandmodelHPkW
FordMustang304224
FordMustang GT350392
BMWM3 GTS444
BMWM5560412

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:

brandmodelPower
FordMustang224
FordMustang223.529
FordMustang GT350392
BMWM3 GTS326.470
BMWM5412
BMWM5411.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.

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_189940_Pic1.JPG

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

View solution in original post

7 Replies
Anonymous
Not applicable
Author

For me, both are two different fields.

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

rubenmarin

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

Not applicable
Author

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.

cwolf
Creator III
Creator III

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;

MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_189940_Pic1.JPG

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

Not applicable
Author

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

Not applicable
Author

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