Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Loading Specific format of Value from rows

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

Untitled.png

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

Re: Loading Specific format of Value from rows

7 Replies
Highlighted
Specialist III
Specialist III

Re: Loading Specific format of Value from rows

Hi,

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

Thanks,

Mukram.

Highlighted
MVP
MVP

Re: Loading Specific format of Value from rows

Try like:

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

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

Highlighted
Partner
Partner

Re: Loading Specific format of Value from rows

Provide Sample data

Highlighted
Contributor III
Contributor III

Re: Loading Specific format of Value from rows

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'

]

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

Untitled.png

Highlighted
Specialist III
Specialist III

Re: Loading Specific format of Value from rows

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.

Highlighted
Specialist III
Specialist III

Re: Loading Specific format of Value from rows

Highlighted
Contributor III
Contributor III

Re: Loading Specific format of Value from rows

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