Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need Help on Function Subfield

Hi All,

I am new to qlikview so need help...

I have a scenario with input data as below-

   

namestatusmarks
gauravmedium|highten|eleven
nikhillow|highestthree|two

Here column Status and marks have values separated by pipe('|') delimiter.

I require output like-

   

namestatusmarks
gauravmediumten
gauravhigheleven
nikhillowthree
nikhilhighest

two

I used  following code -

ABC1:
LOAD name,
status,
age
FROM
C:\USERS\guptga\Desktop\trash\Test_23Sep.xlsx
(
ooxml, embedded labels, table is Sheet2);

CDE1:
NoConcatenate
LOAD  name,
SubField(age,'|')as age,
SubField(status,'|') as status

RESIDENT ABC1;
DROP table ABC1;

I am getting output like this-

   

namestatusage
gauravmediumten
gauravhighten
gauravmediumeleven
gauravhigheleven
nikhillowthree
nikhilhighestthree
nikhillowtwo
nikhilhighesttwo

   

Please help to correct me on this.

Thanks

4 Replies
Michael_Duval
Employee
Employee

Try using the Subfield function to load two tables where the 3rd argument of the Subfield function for the first table returns the 1st substring before the pipe and the 2nd table returns the 2nd substring after the pipe.

Since the field names and the number of fields will be exactly the same Sense will automatically concatenate into one table.

Code would look like this:

CDE1:

NoConcatenate

LOAD  name,

SubField(marks,'|',1) as marks,

SubField(status,'|',1) as status

RESIDENT ABC1;

CDE2:

NoConcatenate

LOAD  name,

SubField(marks,'|',2) as marks,

SubField(status,'|',2) as status

RESIDENT ABC1;

DROP table ABC1;

ecolomer
Master II
Master II

Try this:

ABC1:
LOAD

     name,
     status,
     age
FROM
C:\USERS\guptga\Desktop\trash\Test_23Sep.xlsx
(
ooxml, embedded labels, table is Sheet2);


LOAD 

     name,

     SubField(marks,'|',1) as marks,

     SubField(status,'|',1) as status

RESIDENT ABC1;

Concatenate
LOAD 

     name,

     SubField(marks,'|',2) as marks,

     SubField(status,'|',2) as status

RESIDENT ABC1;
DROP table ABC1;

mithilesh17
Partner
Partner

Hi,

Here you go..

HTH!

Regards,

MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_182016_Pic1_.JPG

LOAD RowNo() as ID,

    name,

    SubField(status,'|',IterNo()) as status,

    SubField(marks,'|',IterNo()) as marks 

Inline [

name        status                     marks

gaurav      medium|high                ten|eleven

nikhil      low|highest                three|two

Bob         low|medium|high            three|two|one

John        low|medium|high|highest    four|five|six

Jane        low|medium|high            four|five|six|seven

](delimiter is spaces)

While IterNo()<=RangeMax(SubStringCount(status,'|'),SubStringCount(marks,'|'))+1;

hope this helps

regards

Marco