Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
50ShadesOfSalty
Partner - Contributor III
Partner - Contributor III

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!

 

Labels (2)
1 Solution

Accepted Solutions
segador_
Partner - Contributor III
Partner - Contributor III

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.

View solution in original post

2 Replies
segador_
Partner - Contributor III
Partner - Contributor III

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
Partner - Contributor III
Partner - Contributor III
Author

This did the trick! Thanks!