topic Need Help on Function Subfield in QlikView

Need Help on Function Subfield

Wed, 23 Sep 2015 12:03:45 GMT

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

Re: Need Help on Function Subfield

Michael_Duval — Thu, 24 Sep 2015 17:59:11 GMT

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;

Re: Need Help on Function Subfield

ecolomer — Thu, 24 Sep 2015 18:06:13 GMT

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;

Re: Need Help on Function Subfield

mithilesh17 — Thu, 24 Sep 2015 18:25:11 GMT

Hi,

Here you go..

HTH!

Regards,

Re: Need Help on Function Subfield

MarcoWedel — Thu, 24 Sep 2015 22:03:35 GMT

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