Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
For me, both are two different fields.
If both are same then For Ford 304 & 224, should be same?
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 ...
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.
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;
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
Thank you very much. Your solution works fine. But I'm going with MarcoWedels solution. It better fits my needs.
Thank you very much. A quite elegant solution, didn't thought about using cross tables.