Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
Could you help me with the following issue,
My data coming from source is like in the following format:
SysDate | IPRange |
11/10/2017 | IP1 IP2 IP3 IP4 IP5 |
My requirement is, data to be shown like below:
SysDate | IPRange |
11/10/2017 | IP1 |
11/10/2017 | IP2 |
11/10/2017 | IP3 |
11/10/2017 | IP4 |
11/10/2017 | IP5 |
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
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:
Hi Ramesh,
is there any delimiter in between like ',','_','|','/'
Thanks,
Arvind Patil
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;
Assuming a space delimiter:
LOAD SysDate,
Subfield(IPRange,' ') as IPRange
FROM ....;
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:
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
In short...
YIC Neil
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: