Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rchittitcs
New Contributor

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
Esteemed Contributor

Re: My string separated by delimiter to be generate as new record

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

7 Replies
arvind_patil
Valued Contributor II

Re: My string separated by delimiter to be generate as new record

Hi Ramesh,

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

Thanks,

Arvind Patil

Highlighted
shraddha_g
Honored Contributor III

Re: My string separated by delimiter to be generate as new record

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;

MVP
MVP

Re: My string separated by delimiter to be generate as new record

Assuming a space delimiter:

LOAD SysDate,

          Subfield(IPRange,' ') as IPRange

FROM ....;

arvind_patil
Valued Contributor II

Re: My string separated by delimiter to be generate as new record

Please look into below thread:

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

Thanks

Arvind Patil

omarbensalem
Esteemed Contributor

Re: My string separated by delimiter to be generate as new record

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

neil_gabin
New Contributor III

Re: My string separated by delimiter to be generate as new record

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
Esteemed Contributor

Re: My string separated by delimiter to be generate as new record

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