Delimiter with multiple fields

[Anonymous]

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

[kaushiknsolanki]

Hi,

Sorry but it is difficult to understand anything.

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

Regards,

Kaushik Solanki

[sibusiso90]

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

[antoniotiman]

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

[Anonymous]

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

[ariel_klien · Accepted solution]

Hi,

Please see attachment.

BR

Ariel

[antoniotiman]

You wrote

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

[Digvijay_Singh]

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;