topic Re: Loading Specific format of Value from rows in QlikView

Loading Specific format of Value from rows

atlantis_shahri — Wed, 15 Nov 2017 07:48:55 GMT

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

Re: Loading Specific format of Value from rows

mdmukramali — Wed, 15 Nov 2017 08:47:36 GMT

Hi,

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

Thanks,

Mukram.

Re: Loading Specific format of Value from rows

tresB — Wed, 15 Nov 2017 08:59:30 GMT

Try like:

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

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

Re: Loading Specific format of Value from rows

shraddha_g — Wed, 15 Nov 2017 09:03:55 GMT

Provide Sample data

Re: Loading Specific format of Value from rows

atlantis_shahri — Wed, 15 Nov 2017 11:08:29 GMT

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'

]

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

Re: Loading Specific format of Value from rows

mdmukramali — Wed, 15 Nov 2017 11:37:36 GMT

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.

Re: Loading Specific format of Value from rows

mdmukramali — Wed, 15 Nov 2017 11:39:57 GMT

Hi,

FYI.

Re: Loading Specific format of Value from rows

atlantis_shahri — Wed, 15 Nov 2017 17:35:01 GMT

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