Loading Specific format of Value from rows

[atlantis_shahri]

I want to load only the previous km value in a single column and running km value in a single column.For example -

previous km       Running Km

51832                95401

34712                52865

51832                110065

Something like this. So far the solutions I found that start with 'p' and 'r' but doesn't provide a specific format like 'previous km-123456' or 'running km-654321'.

Any help is much appreciated and thanks in advance

Regards

Shahriar

[mdmukramali]

Hi,

Can you share Sample data Application so that we can help you in better way,

Thanks,

Mukram.

[tresesco]

Try like:

If(SubField(Data, ',') Like 'p*', Keepchar(SubField(Data, ',') ,'0123456789')) as PreviousKM

If(SubField(Data, ',') Like 'r*', Keepchar(SubField(Data, ',') ,'0123456789')) as RunningKM

[shraddha_g]

Provide Sample data

[atlantis_shahri]

I tried as you suggested Tresesco B. Outcome is not quite what I need. Here the source data format is a mess . I need character limit.  After finding 'previous km-123456,'  once I need to stop the check in the row. In this case it goes all the way through the row. Here 'DMS-11-1772' shows as '111772' which shouldn't be there . Please help. Thanks in advance.

Regards

Shahriar

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Tab:

Load

Data,

If(SubField(Data, ',') Like 'previous km-*', Keepchar(SubField(Data, ','), '0123456789')) as Previous

,If(SubField(Data, ',') Like 'running km-*', Keepchar(SubField(Data, ','), '0123456789')) as Running;

Load * Inline [

Data

'Brand: Bridgestone, DMS-11-1772,previous km-51832,running km-95401,total km-43569,tyre sl:102711,4765466 / 4 Stückfgh / 11-1991'

'Brand: Bridgestone, DMS-11-1992,previous km-34712,running km-52865,total 18152,tyre sl:66474562513/ 4 Stück / 11-1992'

'DMS-11-1995,previous km-78011,running km-108318,total km-30307,DMS-11-1995,DMS-11-1997,DMS-11-1999,DMS-11-2000'

'Use at factory(previous km-54468,running km-94927,total km-38459'

'For DMS-11-1768(use at factory)Ashok leyland Dost,previous km-105000,running km-159326,total km-54326'

'Last replace=24.05.2017,Previus km-103105,present km-107307,DMS-11-1995,total km-4202(use at factory)'

'DM Sha-11-2873, 11-2874,11-2875 Toyace.DM.Sha-14-0341,DM.Sha-11-2013,DM.Sha-11-2014'

]

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

[mdmukramali]

Hi,

Can you try the below script:

Load *,

Replace(SubField(Mid(Data,Index(Data,'previous km-')),',',1),'previous km-','') as PreviousKM,

Replace(SubField(Mid(Data,Index(Data,'running km-')),',',1),'running km-','') as RunningKM

Inline [

Data

'Brand: Bridgestone, DMS-11-1772,previous km-51832,running km-95401,total km-43569,tyre sl:102711,4765466 / 4 Stückfgh / 11-1991'

'Brand: Bridgestone, DMS-11-1992,previous km-34712,running km-52865,total 18152,tyre sl:66474562513/ 4 Stück / 11-1992'

'DMS-11-1995,previous km-78011,running km-108318,total km-30307,DMS-11-1995,DMS-11-1997,DMS-11-1999,DMS-11-2000'

'Use at factory(previous km-54468,running km-94927,total km-38459'

'For DMS-11-1768(use at factory)Ashok leyland Dost,previous km-105000,running km-159326,total km-54326'

'Last replace=24.05.2017,Previus km-103105,present km-107307,DMS-11-1995,total km-4202(use at factory)'

'DM Sha-11-2873, 11-2874,11-2875 Toyace.DM.Sha-14-0341,DM.Sha-11-2013,DM.Sha-11-2014'

];

Thanks,

Mukram.

[mdmukramali · Accepted solution]

Hi,

FYI.

[atlantis_shahri]

Thank you very much Mohammed Mukram. It works just fine.