Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Convert value in loadscript

Hi,

 

i need to find and convert values in columns. 

i have values like 12345 x 2 or 12345 x 3

i want to convert the values in load script to like this 

12345(2pcs) or 12345(3pcs) 

how do i do that? 

Labels (1)
4 Replies
Highlighted
Employee
Employee

The subfield function can split out the components and you can concatenate them back together if you like.
eg
Load
    Subfield(FieldName, ' x ', 1) & '(' & Subfield(FieldName, ' x ', 2) &'pcs)' as NewFieldName,

.

.

 

Highlighted
Creator
Creator

Try this-

Subfield(FieldName,'x','1')&'('&SubField(FieldName,'x',2)&'Pcs'&')'

For Example- if your data is 12345x2 so the evaluation like this

=SubField('12345 x 2','x',1)&'('&SubField('12345 x 2','x',2)&'Pcs'&')'

and the output becomes like this-

clipboard_image_0.png

Sumit Kumar Srivastava
Highlighted
Partner
Partner

Hi,

maybe you could try like this

TAB:
LOAD *,
RowNo () as ID_ROW
;
LOAD * Inline [
TEST
12345 x 2 or 12345 x 3
];

NoConcatenate
TAB_1:
LOAD
Concat(test1,' or ') as TEST,
ID_ROW
Group By
ID_ROW
;
LOAD
ID_ROW,
SubField(TEST,' ',1) & '(' & Trim(SubField(test1,'x',2)) & 'pcs)' as test1,
TEST
;
LOAD
ID_ROW,
SubField(TEST,'or') as test1,
TEST
Resident TAB;

DROP Table TAB;

Highlighted
MVP
MVP

If you need multiple values from single one, you might try like:

TAB:
LOAD *,
 	SubField(TEST, 'x',1) as Num
 While IterNo()<=SubField(TEST, 'x',2);
 	
;
LOAD * Inline [
TEST
12345 x 2
12399 x 3
];

Capture.PNG