Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
Recently i've been trying to load data in a xml format, and i've been doing a lot of transformations in order to make the data readable. Currently, this is my table in QlikSense:
Originally the values were all in the in the Same field, but i needed to have the Odd rows in the Field A and the Pair rows in the FieldB.
From here, i need to transform the current Table to have the following look:
Basically, i would like to do some sort of concatenation in order to have all the rows filled. Instead of having one row for each value of fieldA and another row for fieldB, i would like to have a single row for both fieldA and fieldB.
Someone can help me on this question?
Obs - Data Sample used in the examples:
ExampleActual:
LOAD * INLINE [
id, FieldA, FieldB
e1, A, -
e2, -, B
e3, C, -
e4, -, D
e5, E, -
e6, -, F
];
ExampleObjective:
LOAD * INLINE [
id, FieldA, FieldB
e1, A, B
e2, C, D
e3, E, F
];
Thanks for the help!
Hi!
Try this script:
ExampleActual:
LOAD * INLINE [
id, FieldA, FieldB
e1, A, -
e2, -, B
e3, C, -
e4, -, D
e5, E, -
e6, -, F
];
ExampleObjective:
NOCONCATENATE LOAD
id,
FieldA,
FieldB
WHERE (not IsNull(FieldB)) ;
NOCONCATENATE LOAD
id,
FieldA,
if(FieldA <> '-' and not IsNull(FieldA),Previous(FieldB)) as FieldB
RESIDENT ExampleActual
ORDER BY id DESC;
DROP TABLE ExampleActual;
This is the result of script execution:
Hi!
Try this script:
ExampleActual:
LOAD * INLINE [
id, FieldA, FieldB
e1, A, -
e2, -, B
e3, C, -
e4, -, D
e5, E, -
e6, -, F
];
ExampleObjective:
NOCONCATENATE LOAD
id,
FieldA,
FieldB
WHERE (not IsNull(FieldB)) ;
NOCONCATENATE LOAD
id,
FieldA,
if(FieldA <> '-' and not IsNull(FieldA),Previous(FieldB)) as FieldB
RESIDENT ExampleActual
ORDER BY id DESC;
DROP TABLE ExampleActual;
This is the result of script execution:
This did the trick! Thanks!