Qlik Sense - Split one filed in multiple fileds

[#1]

Hello experts!

As a result of a REST connection, I have this table:

Field1	RowNo
abc	1
asdfg	1
50%	1
31	1
abd	2
zxcv	2
25%	2
51	2

The first field should be in 4 different fields, with the RowNo indicatind the number of the row.

How can I transform the table into something like this?

F1	F2	F3	F4
abc	asdfg	50%	31
abd	zxcv	25%	51

Thank you very much!

[rahulpawarb]

May be this:

Data:

LOAD Field1,

     RowNo

FROM

[https://community.qlik.com/thread/259408?sr=stream&ru=239207]

(html, codepage is 1252, embedded labels, table is @1);

FinalData:

LOAD  DISTINCT

             FieldValue('Field1',1) AS F1

            ,FieldValue('Field1',2) AS F2

            ,FieldValue('Field1',3) AS F3

            ,FieldValue('Field1',4) AS F4

Resident Data

Where RowNo = 1;

Concatenate

LOAD  DISTINCT

             FieldValue('Field1',5) AS F1

            ,FieldValue('Field1',6) AS F2

            ,FieldValue('Field1',7) AS F3

            ,FieldValue('Field1',8) AS F4

Resident Data

Where RowNo = 2;

DROP Table Data;

[tomasz_tru · Accepted solution]

Or maybe with CONCAT, SUBFIELD this way:

input:

LOAD *, RowNo() AS InputRowNumber;

LOAD * INLINE [

Field1,RowNo

abc,1

asdfg,1

50%,1

31,1

abd,2

zxcv,2

25%,2

51,2

];

output:

LOAD

Subfield(OneString, ';',1) AS F1,

Subfield(OneString, ';',2) AS F2,

Subfield(OneString, ';',3) AS F3,

Subfield(OneString, ';',4) AS F4;

LOAD

CONCAT(Field1,';', InputRowNumber) AS OneString RESIDENT input

GROUP BY RowNo;

[#4]

thank you very much Rahul!

This could works, but the fact is that the table i showed was just an example of a much much longer table (in which I don't know hor many rows and rowno i have, i can't repeat this method thousands of times in the load script).

Any further suggestions?

Thank you again!!

[#5]

thank you very much Tomasz!

This could works, but the fact is that the table i showed was just an example of a much much longer table (in which I don't know hor many rows and rowno i have, i can't repeat this method thousands of times in the load script).

Any further suggestions?

Thank you again!!

[tomasz_tru]

Load your data instead of INLINE LOAD part and it should work no matter how many rows there are.

[tomasz_tru]

Or maybe there might be more 'rowNo' than 4 for one final row (at the end you want columns F1:Fx where x could be greater than 4)?

[simondachstr]

A generic load is required.....

Generic ‒ QlikView