Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
nareshthavidishetty
Contributor III

Trim field

Hi ,

We need to trim field based on some conditions as below.

Please find the attached spreadsheet for more detail reference.

   

Supplier IDSupplier IDExpected Result
0000112553_1300C0_112553112553
0000112554_1200C1_112554112554
0000112555_3456F1_112555112555
0000112556_4568H0_112556112556
0000112557_6544H1_112557112557
0000112558_2456H2_112558112558
Remove Zeros at starting and remove detail after '_' (example  112553)Remove detail before '_'   (example 112553)

Thanks..

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Trim field

Try this

Table:

LOAD SubField(F1, '_', 1) * 1 as Expected1,

SubField(F2, '_', 2) as Expected2,

F1,

F2;

LOAD * INLINE [

    F1, F2

    0000112553_1300, C0_112553

    0000112554_1200, C1_112554

    0000112555_3456, F1_112555

    0000112556_4568, H0_112556

    0000112557_6544, H1_112557

    0000112558_2456, H2_112558

];

View solution in original post

5 Replies
Highlighted

Re: Trim field

What are the C,F,H ??

May be look SubField() function ?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
nareshthavidishetty
Contributor III

Re: Trim field

Hi,


These were the prefix for the ID's.

The motive is to get both supplierId's from two different sources into one format.

Thanks..

Highlighted

Re: Trim field

Try this

Table:

LOAD SubField(F1, '_', 1) * 1 as Expected1,

SubField(F2, '_', 2) as Expected2,

F1,

F2;

LOAD * INLINE [

    F1, F2

    0000112553_1300, C0_112553

    0000112554_1200, C1_112554

    0000112555_3456, F1_112555

    0000112556_4568, H0_112556

    0000112557_6544, H1_112557

    0000112558_2456, H2_112558

];

View solution in original post

Highlighted

Re: Trim field

That means you have 2 fields already. So SubField() works for you?? Look in help

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
paulinabrzozows
New Contributor

Re: Trim field

Hi

Maybe try 'Mid' function:

mid(FieldName,StartNum,EndNum) as NewValue

mid([Supplier ID], 5, 10) as New Value

I hope that helps.

Kind Regards

Paulina