Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nareshthavidishetty
Creator III
Creator 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..

1 Solution

Accepted Solutions
sunny_talwar

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
Anil_Babu_Samineni

What are the C,F,H ??

May be look SubField() function ?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
nareshthavidishetty
Creator III
Creator III
Author

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..

sunny_talwar

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

];

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
Anonymous
Not applicable

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