topic Re: How to load data only between first "_" marks in QlikView

How to load data only between first "_" marks

Anonymous — Mon, 14 Dec 2015 12:53:26 GMT

Hello,

Just a simple question I didn't figured out by myself.

I have data that has a column [File_name] with data like:

AB_0008417891_XXYY1410_30.11.2015_20151201125515_151002228001.PDF

BC_44028549_AA_S.afp

How do I read only data between first "_" marks? I want my loaded data look like:

0008417891

44028549

Thanks for help.

- Risto

Re: How to load data only between first "_" marks

sunny_talwar — Mon, 14 Dec 2015 13:03:01 GMT

May be try SubField() function:

SubField(File_name, '_', 2)

Table:

LOAD *,

SubField(File_name, '_', 2) as NewName;

LOAD * Inline [

File_name

AB_0008417891_XXYY1410_30.11.2015_20151201125515_151002228001.PDF

BC_44028549_AA_S.afp

];

You can also use

TextBetween(File_name, '_', '_', 1) as NewName2

Re: How to load data only between first "_" marks

its_anandrjs — Mon, 14 Dec 2015 13:09:20 GMT

Then write

=SubField([File_name],'_',1)

it gives

And

=SubField([File_name],'_',2)

it gives

0008417891

44028549

And

=Left([File_name],2)

Check this Example

LOAD *,SubField(Test,'_',1) as Field1,
SubField(Test,'_',2) as Field2,
Left(Test,2) as Field3;

LOAD * Inline
[
Test
AB_0008417891_XXYY1410_30.11.2015_20151201125515_151002228001.PDF
BC_44028549_AA_S.afp
];

Regards

Anand

Re: How to load data only between first "_" marks

Anonymous — Mon, 14 Dec 2015 13:42:38 GMT

Thanks Sunny T!

I used that TextBetween function. Works flawlessly!

- Risto