Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Sorry but it is difficult to understand anything.
Please give us sample data and try to explain with example.
Regards,
Kaushik Solanki
Can you explain of show us how the final thing should look like based on the values provided there
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
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
Hi,
Please see attachment.
BR
Ariel
You wrote
Sales Name | Field 1 | Field 2 |
CV_425150_dsfsdfdsd_graphic | 425150 | Null |
FG_sdfsdfsadfas_434040_Book | Null | 434040 |
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;