Need Help on Function Subfield

[#1]

Hi All,

I am new to qlikview so need help...

I have a scenario with input data as below-

name	status	marks
gaurav	medium|high	ten|eleven
nikhil	low|highest	three|two

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

I require output like-

name	status	marks
gaurav	medium	ten
gaurav	high	eleven
nikhil	low	three
nikhil	highest	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-

name	status	age
gaurav	medium	ten
gaurav	high	ten
gaurav	medium	eleven
gaurav	high	eleven
nikhil	low	three
nikhil	highest	three
nikhil	low	two
nikhil	highest	two

Please help to correct me on this.

Thanks

[Michael_Duval]

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]

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]

Hi,

Here you go..

HTH!

Regards,

[MarcoWedel]

Hi,

one solution could be:

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