Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

My string separated by delimiter to be generate as new record

Hi Community,

Could you help me with the following issue,

My data coming from source is like in the following format:

  

SysDateIPRange
11/10/2017IP1 IP2 IP3 IP4 IP5

My requirement is, data to be shown like below:

  

SysDateIPRange
11/10/2017IP1
11/10/2017IP2
11/10/2017IP3
11/10/2017IP4
11/10/2017IP5

This may be a very basic question but, as i'm a beginner with Qlik  could help me to resolve this out, please.

-Thanks in advance

Ramesh

1 Solution

Accepted Solutions
OmarBenSalem

Try this:

//this is ur original able:

range:

load *,SubStringCount(IPRange,' ') as  NewField;

load * Inline [

SysDate, IPRange

11/10/2017, IP1 IP2 IP3 IP4 IP5

];

Let vLength = Peek('NewField',0, 'range');

for i=1 to $(vLength)+1

Tab:

LOAD SysDate, SubField(IPRange,' ',$(i)) as IPRange

Resident range ;

NEXT i;

drop table range;

result:

Capture.PNG

View solution in original post

7 Replies
arvind_patil
Partner - Specialist III
Partner - Specialist III

Hi Ramesh,

is there any delimiter in between like ',','_','|','/'

Thanks,

Arvind Patil

shraddha_g
Partner - Master III
Partner - Master III

Try,

Table1:

LOAD *,SubStringCount(IPRange,' ') as test Inline [

SysDate, IPRange

11/10/2017, IP1 IP2 IP3 IP4 IP5

];

for i=1 to 5

Table2:

LOAD *, SubField(IPRange,' ',$(i)) as NewIPRange

Resident Tab2 ;

NEXT I;

swuehl
MVP
MVP

Assuming a space delimiter:

LOAD SysDate,

          Subfield(IPRange,' ') as IPRange

FROM ....;

arvind_patil
Partner - Specialist III
Partner - Specialist III

Please look into below thread:

https://community.qlik.com/thread/221516 

Thanks

Arvind Patil

OmarBenSalem

Try this:

//this is ur original able:

range:

load *,SubStringCount(IPRange,' ') as  NewField;

load * Inline [

SysDate, IPRange

11/10/2017, IP1 IP2 IP3 IP4 IP5

];

Let vLength = Peek('NewField',0, 'range');

for i=1 to $(vLength)+1

Tab:

LOAD SysDate, SubField(IPRange,' ',$(i)) as IPRange

Resident range ;

NEXT i;

drop table range;

result:

Capture.PNG

Anonymous
Not applicable
Author

Hey Ramesh

Michael Tarallo has a great video explaining how to do exactly what you are looking for

https://www.youtube.com/watch?v=D_03gU8GvK8

2017-11-10_221848.jpg

In short...

  1. Go to the Data Manager
  2. Select your table that has the fields
  3. Click the Edit icon (pencil) at the bottom center of the page
  4. Click the Unpivot button at the top right of the page
  5. Now select the columns (just above the column name) and press Unpivot again
  6. You will need to rename the new columns.

2017-11-10_223455.jpg

YIC Neil

OmarBenSalem

If you have multiple rows:

I mean:

11/10/2017, IP1 IP2 IP3 IP4 IP5

12/10/2017, IP1 IP2 IP3 IP4 IP5 IP6

13/10/2017, IP1 IP2 IP3 IP4 IP5 IP6 IP7

do as follow:

range:

Load *, max(NewField) as MaxLentgh group by SysDate,IPRange,NewField ;

load *,SubStringCount(IPRange,' ') as  NewField;

load * Inline [

SysDate, IPRange

11/10/2017, IP1 IP2 IP3 IP4 IP5

12/10/2017, IP1 IP2 IP3 IP4 IP5 IP6

13/10/2017, IP1 IP2 IP3 IP4 IP5 IP6 IP7

];

length:

NoConcatenate

load max(MaxLentgh) as Length Resident range;

Let vLength = Peek('Length',0, 'length');

for i=1 to $(vLength)+1

Tab:

LOAD SysDate, SubField(IPRange,' ',$(i)) as IPRange

Resident range ;

NEXT i;

drop table range;

drop table length;


result:

Capture.PNG