topic Re: Extract numbers from one line field in QlikView

Extract numbers from one line field

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

From the below field, how do I extract numbers

for example:

004583

0002195

001

20040921

I am using the trim left and mid function this is the right function to use?

Thanks in advance.

Re: Extract numbers from one line field

jwjackso — Wed, 03 Jul 2019 18:20:58 GMT

If the fields are fixed width, that may be the easiest method. It looks like most of the fields are space delimited, you might want to look at the subfield function

Re: Extract numbers from one line field

Vegar — Wed, 03 Jul 2019 18:38:08 GMT

Try this

LOAD

subfield ([Your numbersfield], ' ') as [Extracted number],

...

FROM source;

Re: Extract numbers from one line field

has75042 — Wed, 03 Jul 2019 19:21:17 GMT

subfield function seems to be working.

SubField(ABNOTE, ' ', -1) is giving me this # 0200709190660, how do I convert this number into date.

Re: Extract numbers from one line field

sasiparupudi1 — Wed, 03 Jul 2019 19:22:57 GMT

SubField('004583 0002195 001 20040921',' ',1)

SubField('004583 0002195 001 20040921',' ',2)

SubField('004583 0002195 001 20040921',' ',3)

SubField('004583 0002195 001 20040921',' ',4)

Re: Extract numbers from one line field

has75042 — Fri, 05 Jul 2019 14:48:46 GMT

Hey,

How can I convert this # 0201010310600 into date.

I have tired make date function, floor. None of them seems to be working. Please let me know a function that I can use to convert above number into date

Thanks in advance.

Re: Extract numbers from one line field

jwjackso — Fri, 05 Jul 2019 15:01:20 GMT

How does the number 0201010310600 represent a date? Is it Oct 31 2019 at 6 am? Why does it have a leading zero. Is it the number of milliseconds, seconds, minutes from a date?

Re: Extract numbers from one line field

has75042 — Fri, 05 Jul 2019 15:06:39 GMT

Yes, it is Oct 31 2019 at 6 am. The leading zero doesn't represent anything. I need to remove that leading zero

Re: Extract numbers from one line field

jwjackso — Fri, 05 Jul 2019 15:36:33 GMT

The MakeDate function worked. I would have expected the Date# function to work.

=MakeDate(Num#(Mid(DT,2,4)),Num#(Mid(DT,6,2)),Num#(Mid(DT,8,2)))

Re: Extract numbers from one line field

sasiparupudi1 — Sat, 06 Jul 2019 15:25:57 GMT

Try Timestamp

=Timestamp(Timestamp#(Num(A),'YYYYMMDDhhmm'))