Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
50ShadesOfSalty
New Contributor

Concatenating the rows inside the Table

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:

ExampleA.png

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:

2.png

 

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!

 

1 Solution

Accepted Solutions
segador_
New Contributor III

Re: Concatenating the rows inside the Table

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:

2.png

 

Regards, Igor.
2 Replies
segador_
New Contributor III

Re: Concatenating the rows inside the Table

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:

2.png

 

Regards, Igor.
50ShadesOfSalty
New Contributor

Re: Concatenating the rows inside the Table

This did the trick! Thanks!