topic Re: Delimiter with multiple fields in QlikView

Delimiter with multiple fields

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

Would you please help me to delimit Sales Name field to Field 1 and Field 2. Please refer the table below

Sales Name	Field 1	Field 2
CV_425150_dsfsdfdsd_graphic	425150	Null
FG_sdfsdfsadfas_434040_Book	Null	434040
EH_425258_434039_Infographic	425258	434039
MM_425332_433967_SAW	425332	433967
AA_422335_510035507_510035509_Asset_sdf_sda	510035507	510035509
AA_422335_430035508_310035534_Asset_sdf_sda	430035508	310035534

Condition

2nd delimiter number would be F1
3rd delimiter number would be F2
If 3 numbers comes 3rd delimiter number would be F1
If 3 numbers comes 4th delimiter number would be F2
If other than numbers it would be NULL

Re: Delimiter with multiple fields

kaushiknsolanki — Tue, 23 May 2017 13:00:19 GMT

Hi,

Sorry but it is difficult to understand anything.

Please give us sample data and try to explain with example.

Regards,

Kaushik Solanki

Re: Delimiter with multiple fields

sibusiso90 — Tue, 23 May 2017 13:25:28 GMT

Can you explain of show us how the final thing should look like based on the values provided there

Re: Delimiter with multiple fields

antoniotiman — Tue, 23 May 2017 13:38:50 GMT

Maybe

LOAD *,
If(IsNum(SubField(SalesName,'_',4)),SubField(SalesName,'_',3),
If(IsNum(SubField(SalesName,'_',2)),SubField(SalesName,'_',2))) as F1,
If(IsNum(SubField(SalesName,'_',4)),SubField(SalesName,'_',4),
If(IsNum(SubField(SalesName,'_',3)),SubField(SalesName,'_',3))) as F2
Inline [
SalesName
CV_425150_dsfsdfdsd_graphic
FG_sdfsdfsadfas_434040_Book
EH_425258_434039_Infographic
MM_425332_433967_SAW
AA_422335_510035507_510035509_Asset_sdf_sda
AA_422335_430035508_310035534_Asset_sdf_sda
];

Regards,

Antonio

Re: Delimiter with multiple fields

Anonymous — Tue, 23 May 2017 13:43:10 GMT

after 2nd delimiter of Sales Name is Number then take that as Field 1 other wise NULL.

after 3rd delimiter of Sales Name is Number then take that as Field 2 other wise NULL.

In the Sales name (last two entry) ignore 2nd delimit, if 3rd and 4th comes with number

Re: Delimiter with multiple fields

ariel_klien — Tue, 23 May 2017 13:47:46 GMT

Hi,

Please see attachment.

Ariel

Re: Delimiter with multiple fields

antoniotiman — Tue, 23 May 2017 13:53:05 GMT

You wrote

Sales Name	Field 1	Field 2
CV_425150_dsfsdfdsd_graphic	425150	Null
FG_sdfsdfsadfas_434040_Book	Null	434040

Re: Delimiter with multiple fields

Digvijay_Singh — Tue, 23 May 2017 14:04:06 GMT

Data:

Load * inline [

Sales Name

CV_425150_dsfsdfdsd_graphic

FG_sdfsdfsadfas_434040_Book

EH_425258_434039_Infographic

MM_425332_433967_SAW

AA_422335_510035507_510035509_Asset_sdf_sda

AA_422335_430035508_310035534_Asset_sdf_sda ];

Final:

Load

[Sales Name],

if(substringcount(keepchar([Sales Name],'0123456789_'),'_')<=3,

if (len(TextBetween(keepchar([Sales Name],'0123456789_'),'_','_',1))>0,

TextBetween([Sales Name],'_','_',1),Null()),

if (len(TextBetween(keepchar([Sales Name],'0123456789_'),'_','_',3))>0,

TextBetween([Sales Name],'_','_',2),Null())) as Field1,

if(substringcount(keepchar([Sales Name],'0123456789_'),'_')<=3,

if (len(TextBetween(keepchar([Sales Name],'0123456789_'),'_','_',2))>0,

TextBetween([Sales Name],'_','_',2),Null()),

if (len(TextBetween(keepchar([Sales Name],'0123456789_'),'_','_',3))>0,

TextBetween([Sales Name],'_','_',3),Null())) as Field2

Resident Data;