Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
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.