topic Re: Trimming Data in QlikView

Trimming Data

mccook — Wed, 04 Jun 2014 10:52:32 GMT

Hi,

I have data like below being loaded into a column called Name:

Row 1

8932h3*(&"*29Default*&38 agg\Dean McCook 8932h3*(&"*29Default*&38 agg\Tim Saddler

Row 2

8932h3*(&"*29Default*&38 agg\Gavin Kite

The desired result I want is:

Name:

Default*&38 agg\Gavin Kite

29Default*&38 agg\Tim Saddler

Which I will then tidy up to be (I'm ok with this bit)

Name:

Gavin Kite

Tim Saddler

My current result is:

Dean McCook

Tim Saddler

Gavin Kite

As I don't know a way of trimming row 1 to remove the first part, the logic is keep anything to the right of the last instance of Default in the string, any help would be appreciated.

Thanks,

Dean

Re: Trimming Data

tresB — Wed, 04 Jun 2014 11:12:09 GMT

Try:

Load

SubField(Name, '/' , -1) // -1 refers the last substring

Re: Trimming Data

Wed, 04 Jun 2014 11:13:12 GMT

What about this?:

tab:

LOAD rowno() as id,* INLINE [

'8932h3*(&"*29Default*&38 agg\Dean McCook 8932h3*(&"*29Default*&38 agg\Tim Saddler'

'8932h3*(&"*29Default*&38 agg\Gavin Kite'

];

tab1:

load RowNo() as sub_id, id, ltrim(SubField(F1,'8932h3*(&"*29Default*&38 agg\')) as name Resident tab;

join(tab1)

load id,max(sub_id) as sub_id,1 as key Resident tab1 Group by id;

tab2:

load name Resident tab1 where key=1;

drop tables tab, tab1;

Re: Trimming Data

mccook — Wed, 04 Jun 2014 11:22:58 GMT

Thanks,

Sorry, I was a little lazy with the rows (copying and pasting), they have different characters and numbers and sometimes there can be more than one name e.g.

Row 1

asfdah3*(&"*19Default*&38 agg\Dean McCook 893ada3*(&"*29Default*&38 agg\Tim Saddler

Row 2

8adfas2h3*(&aaad9Default*&38 agg\Gavin Kite

Row 3

8dfas2h3*(&aaad9Default*&38 agg\Harp Sandu 8aada3*(&"*29Default*&38 agg\Kev Smith agg\Mark Lees

So anytime I see the last instance of the word Default, the name or names after that I want, so in this new example the answer I need is:

Name:

Tim Saddler

Gavin Kite

Kev Smith

Mark Lees

Cheers,

Dean

Re: Trimming Data

mccook — Wed, 04 Jun 2014 11:27:55 GMT

If I can remove/trim the fields of anything before the last instance of default, I can already do the rest.

Cheers,

Dean

Re: Trimming Data

Wed, 04 Jun 2014 11:28:14 GMT

Dean,

you still may cut it once or maybe twice with SubField() and join with max ...

regards

Darek

Re: Trimming Data

mccook — Wed, 04 Jun 2014 11:33:00 GMT

Thanks,

Not sure if this is what you mean, its not giving me the desired results?

LOAD * INLINE [

    Name

    'asfdah3*(&"*19Default*&38 agg\Dean McCook               893ada3*(&"*29Default*&38 agg\Tim Saddler'

    '8adfas2h3*(&aaad9Default*&38 agg\Gavin Kite'

'8dfas2h3*(&aaad9Default*&38 agg\Harp Sandu            8aada3*(&"*29Default*&38 agg\Kev Smith    agg\Mark Lees'

];

test2:

NoConcatenate

Load

SubField(Name, '/' , -1) as Name

Resident test1;

drop table test1;

Re: Trimming Data

Wed, 04 Jun 2014 11:33:46 GMT

after last default:

tab:

LOAD rowno() as id, * INLINE [

'asfdah3*(&"*19Default*&38 agg\Dean McCook 893ada3*(&"*29Default*&38 agg\Tim Saddler'

8adfas2h3*(&aaad9Default*&38 agg\Gavin Kite

'8dfas2h3*(&aaad9Default*&38 agg\Harp Sandu 8aada3*(&"*29Default*&38 agg\Kev Smith agg\Mark Lees'

];

tab1:

load RowNo() as sub_id, id, ltrim(SubField(F1,'Default')) as name Resident tab;

join(tab1)

load id,max(sub_id) as sub_id,1 as key Resident tab1 Group by id;

tab2:

load name Resident tab1 where key=1;

drop tables tab, tab1;

Re: Trimming Data

its_anandrjs — Wed, 04 Jun 2014 11:38:26 GMT

Try with this load script by use of the subfield

Sufield:

LOAD

SubField((SubField(Row,' ',-1)),'\',2) as FinalString,

Row;

LOAD * Inline

[

Row

8932h3*(&"*29Default*&38 agg\Dean McCook 8932h3*(&"*29Default*&38 agg\Tim Saddler

8932h3*(&"*29Default*&38 agg\Gavin Kite

];

This will give out out

FinalString

Gavin Kite

Tim Saddler

Re: Trimming Data

its_anandrjs — Wed, 04 Jun 2014 11:39:36 GMT

Try this in your load script will give as your result please check.

SubField((SubField(Row,' ',-1)),'\',2) as FinalString,

Re: Trimming Data

mccook — Wed, 04 Jun 2014 11:49:37 GMT

Perfect,

Thanks.