Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

khaycock
Contributor

Loading in specific data from a field

I have a field where which holds a lot of text as it is a description field but I would like to only take the first few sets of numbers that are within each field. The user has inputted these sets of numbers at the beginning of the field and are split up by commas. Is there anyway to force the load of the field in the script to only bring in all numbers at the beginning of the field and nothing else?

The data looks like this:

serena.PNG

Then followed by the text.

The field name is [Vulnerability description]. Is this even possible?

1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Loading in specific data from a field

Try this?

LOAD [Item Id], 

     [Expiration Date],

     State,

     [Active/Inactive], 

     [Item Type],

     [Vulnerability description],

     Subfield(KeepChar(Left([Vulnerability description],Index([Vulnerability description], Left(Keepchar([Vulnerability description],'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),1))), ',0123456789'), ',') AS Test

FROM

[..\Yourexcelfilename.xlsx]

(ooxml, embedded labels, table is sheet1);

25 Replies
sudeepkm
Valued Contributor III

Re: Loading in specific data from a field

it would be possible if you could find the characters/string present between the last number and the text starting next.

It could be a space or tab or something else. Using a subfield function you can bring only those numbers from that whole text.

vishsaggi
Esteemed Contributor III

Re: Loading in specific data from a field

May be this?

= KeepChar( [Vulnerability description], ',01234567890')

then if you want to split the numbers for each row use

Subfield(KeepChar( [Vulnerability description], ',01234567890') , ',')

andrey_krylov
Contributor III

Re: Loading in specific data from a field

Hi, Kathryn. Can you post some sample data?

khaycock
Contributor

Re: Loading in specific data from a field

This works in theory but annoyingly I have some numbers further down the descriptions in some case and this also splits them out when I don't want them. Is there anyway to stop it getting data after a certain point? I am able to amend the data set if need be (just can't delete the text part unfortunately)

vishsaggi
Esteemed Contributor III

Re: Loading in specific data from a field

Can you post some sample data with such scenarios and your expected output so that we can come up with some solutions.

khaycock
Contributor

Re: Loading in specific data from a field

I have attached an example..

The scripting you gave me worked as it split up the numbers here into different lines which is what I want:

1.PNG

But it also pulled out any other numbers from the field like this:

2.PNG

I only require the first delimitered number fields. 

vishsaggi
Esteemed Contributor III

Re: Loading in specific data from a field

So in the above example what are the numbers you are expecting to see for example the rows Apache HTTPD or

for the row Solaris etc?

khaycock
Contributor

Re: Loading in specific data from a field

Nothing currently. I only want to use fields with listed numbers at the beginning, like the bottom two in that list.

vishsaggi
Esteemed Contributor III

Re: Loading in specific data from a field

Try this?

LOAD [Item Id], 

     [Expiration Date],

     State,

     [Active/Inactive], 

     [Item Type],

     [Vulnerability description],

     Subfield(KeepChar(Left([Vulnerability description],Index([Vulnerability description], Left(Keepchar([Vulnerability description],'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),1))), ',0123456789'), ',') AS Test

FROM

[..\Yourexcelfilename.xlsx]

(ooxml, embedded labels, table is sheet1);

Community Browser