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: 
khaycock
Creator
Creator

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
Champion III
Champion III

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);

View solution in original post

25 Replies
sudeepkm
Specialist III
Specialist III

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
Champion III
Champion III

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
Specialist
Specialist

Hi, Kathryn. Can you post some sample data?

khaycock
Creator
Creator
Author

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
Champion III
Champion III

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

khaycock
Creator
Creator
Author

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
Champion III
Champion III

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
Creator
Creator
Author

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

vishsaggi
Champion III
Champion III

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);