topic Re: Loading in specific data from a field in QlikView

Loading in specific data from a field

khaycock — Wed, 25 Nov 2020 16:16:04 GMT

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:

Then followed by the text.

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

Re: Loading in specific data from a field

sudeepkm — Fri, 07 Sep 2018 15:30:18 GMT

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.

Re: Loading in specific data from a field

vishsaggi — Fri, 07 Sep 2018 15:32:19 GMT

May be this?

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

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

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

Re: Loading in specific data from a field

andrey_krylov — Fri, 07 Sep 2018 15:35:41 GMT

Hi, Kathryn. Can you post some sample data?

Re: Loading in specific data from a field

khaycock — Mon, 10 Sep 2018 14:36:14 GMT

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)

Re: Loading in specific data from a field

vishsaggi — Mon, 10 Sep 2018 14:58:26 GMT

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

Re: Loading in specific data from a field

khaycock — Mon, 10 Sep 2018 15:15:37 GMT

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:

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

I only require the first delimitered number fields.

Re: Loading in specific data from a field

vishsaggi — Mon, 10 Sep 2018 16:10:49 GMT

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?

Re: Loading in specific data from a field

khaycock — Tue, 11 Sep 2018 07:57:03 GMT

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

Re: Loading in specific data from a field

vishsaggi — Tue, 11 Sep 2018 14:28:22 GMT

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

Re: Loading in specific data from a field

khaycock — Tue, 11 Sep 2018 14:51:22 GMT

Amazing thank you!!!

Re: Loading in specific data from a field

vishsaggi — Tue, 11 Sep 2018 15:18:56 GMT

No Problem.

Re: Loading in specific data from a field

khaycock — Tue, 11 Sep 2018 15:31:10 GMT

If I wanted to use the Subfield statement on another field that has many different delimiters (such as ',' '.' '&' 'and' etc..), is this possible to do all in one line? I've tried like this: Subfield([IP Address],',& .and') AS IPAddress, but didn't seem to work

Re: Loading in specific data from a field

vishsaggi — Tue, 11 Sep 2018 15:35:13 GMT

No Subfield function takes only one delimiter at a time unless the data has ,. as consecutive characters. So probably use multiple subfield() like

Subfield(Subfield(Subfield([IP Address], ',') , '.'), ')')

Re: Loading in specific data from a field

khaycock — Fri, 14 Sep 2018 09:16:45 GMT

Hi Vishwarath, I have another similar question.

I have another field that I need to only take a specific part of it but I'm not sure if I can use the same constraints as above on it.

Here is the field:

CN=1234566,OU=Workstations,OU=Screen,OU=CALA,OU=Dog,OU=Cat,DC=txt,DC=business,DC=com

I only want the information from the second 'OU' to be displayed in my field. So in this case all I want to be displayed is 'Screen'.

Is this possible?

Re: Loading in specific data from a field

andrey_krylov — Fri, 14 Sep 2018 11:21:14 GMT

=Subfield(Subfield([Field], 'OU=', 3), ',')

Re: Loading in specific data from a field

vishsaggi — Fri, 14 Sep 2018 14:32:50 GMT

Try this?

= Subfield(Mid(Description,Index(Description,'OU=',2), Len(Description)), ',', 1)

Re: Loading in specific data from a field

khaycock — Fri, 14 Sep 2018 14:53:02 GMT

That is still showing the OU= part, can we remove that?

Re: Loading in specific data from a field

vishsaggi — Fri, 14 Sep 2018 14:58:24 GMT

Yes, you can use any of below expr

= Subfield(Mid(Description,Index(Description,'OU=',2)+3, Len(Description)), ',', 1)

= PurgeChar(Subfield(Mid(Description,Index(Description,'OU=',2), Len(Description)), ',', 1), 'OU=')

Re: Loading in specific data from a field

khaycock — Wed, 19 Sep 2018 11:02:40 GMT

I'm sorry to keep asking you questions, but they are all similar to the same theme so saves me opening a new thread!

Regarding the comment:

'No Subfield function takes only one delimiter at a time unless the data has ,. as consecutive characters. So probably use multiple subfield() like

Subfield(Subfield(Subfield([IP Address], ',') , '.'), ')')'

I also have fields within the data that have been delimetered using a return space and putting it onto the next line. Am I able to force these within a subfield statement too?