Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Qlik Sense - Split one filed in multiple fileds

Hello experts!

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

Field1RowNo
abc

1

asdfg1

50%

1
311
abd2
zxcv2
25%2
512

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?

F1F2F3F4
abcasdfg

50%

31
abdzxcv25%51

Thank you very much!

1 Solution

Accepted Solutions
tomasz_tru
Valued Contributor

Re: Qlik Sense - Split one filed in multiple fileds

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;

7 Replies
rahulpawarb
Valued Contributor III

Re: Qlik Sense - Split one filed in multiple fileds

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
Valued Contributor

Re: Qlik Sense - Split one filed in multiple fileds

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;

Not applicable

Re: Qlik Sense - Split one filed in multiple fileds

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!!

Not applicable

Re: Qlik Sense - Split one filed in multiple fileds

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
Valued Contributor

Re: Qlik Sense - Split one filed in multiple fileds

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

tomasz_tru
Valued Contributor

Re: Qlik Sense - Split one filed in multiple fileds

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)?

Luminary
Luminary

Re: Qlik Sense - Split one filed in multiple fileds

A generic load is required.....

Generic ‒ QlikView

Community Browser