Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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.

Tags (3)
1 Solution

Accepted Solutions
MarcoWedel
Not applicable

Re: Merging multiple fields into one

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

7 Replies
balrajahlawat
Not applicable

Re: Merging multiple fields into one

For me, both are two different fields.

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

rubenmarin
Not applicable

Re: Merging multiple fields into one

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

Re: Merging multiple fields into one

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.

chrwolf64
Not applicable

Re: Merging multiple fields into one

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
Not applicable

Re: Merging multiple fields into one

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

Re: Merging multiple fields into one

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

Not applicable

Re: Merging multiple fields into one

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