Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
haribabugv
Creator
Creator

Splitting column into multiple columns- no delimiter

Hello All,

Is there any way to split a column into multiple columns? I cannot use subfield because there is no identifier.

I need to divide a column into multiple columns using number of characters. Every 3 characters belong to column

Input:

 my data structure will look this 

Product CodeColumn1Column2
A1100A00A00B00A00A00A00C00A00A00B00C00A00A00B00C00C00C00A00A00A00E00A00A00A00C00A00A00E00C00A00A00E00C00C00C00A
B1100B00D00B00D00A00A00C00A00D00B00F00C00A00D00D00F00D00C00G00G00E00G00G00G00H00G00G00E00H00G00G00E00H00H00H00G

 

00A00A00B00A00A00A00C00A00A00B00C00A00A00B00C00C00C00A

Each cell has 54 characters in one column

Now need to show in output, 18 columns with 3 characters in each cell.

Let me know if i can use subfield here as i am not sure what will be the delimiter.

 

Expected Output

Product 123456789101112131415161718
A11Column100A00A00B00A00A00A00C00A00A00B00C00A00A00B00C00C00C00A
A11Column200A00A00E00A00A00A00C00A00A00E00C00A00A00E00C00C00C00A
B11Column100B00D00B00D00A00A00C00A00D00B00F00C00A00D00D00F00D00C
B11Column200G00G00E00G00G00G00H00G00G00E00H00G00G00E00H00H00H00G

 

Every 3 characters form a cell in the output

00A00A00B00A00A00A00C00A00A00B00C00A00A00B00C00C00C00A

 

Thanks a lot 

Labels (3)
1 Solution

Accepted Solutions
JaMajka1
Partner
Partner

Hello Haribabugv,

I am sending you a simple script for splitting columns by using fixed number of characters. You just have to transform the data in the structure you need, if you want to have them in columns.

 

[input]:
load * Inline
[productcode,data
A11,00A00A00B00A00A00A00C00A00A00B00C00A00A00B00C00C00C00A
B11,00B00D00B00D00A00A00C00A00D00B00F00C00A00D00D00F00D00C
];

[output]:
noconcatenate
load
productcode,
rowno() as row_id,
mid(data,iterno()*3-2,3) as X
resident input
while iterno()<19;

drop table input;

 

Hope it helps,

Maria 🙂

View solution in original post

3 Replies
JaMajka1
Partner
Partner

Hello Haribabugv,

I am sending you a simple script for splitting columns by using fixed number of characters. You just have to transform the data in the structure you need, if you want to have them in columns.

 

[input]:
load * Inline
[productcode,data
A11,00A00A00B00A00A00A00C00A00A00B00C00A00A00B00C00C00C00A
B11,00B00D00B00D00A00A00C00A00D00B00F00C00A00D00D00F00D00C
];

[output]:
noconcatenate
load
productcode,
rowno() as row_id,
mid(data,iterno()*3-2,3) as X
resident input
while iterno()<19;

drop table input;

 

Hope it helps,

Maria 🙂

View solution in original post

Zhandos_Shotan
Partner
Partner

Hi!

First, use Crosstable load to create Column1, Column2... dimension. Then define 18 fields with Mid() function.

Tab1:

CrossTable(Columns, Data) Load

*

from Split.xlsx;

 

Load

Product Code,

Columns,

Mid(Data, 1,3) as "1",

Mid(Data, 4,3) as "2",

Mid(Data, 7,3) as "3",

...

Mid(Data, 51,3) as "18",

Resident Tab1;

 

Its obvious and simple way but not smart Smiley Happy

 

haribabugv
Creator
Creator
Author

Thanks Maria, it worked