Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
50ShadesOfSalty
Partner
Partner

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
Partner

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
Partner

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

50ShadesOfSalty
Partner
Partner
Author

This did the trick! Thanks!