topic Trim field in QlikView

Trim field

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

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

Please find the attached spreadsheet for more detail reference.

Supplier ID	Supplier ID	Expected Result

0000112553_1300	C0_112553	112553
0000112554_1200	C1_112554	112554
0000112555_3456	F1_112555	112555
0000112556_4568	H0_112556	112556
0000112557_6544	H1_112557	112557
0000112558_2456	H2_112558	112558


Remove Zeros at starting and remove detail after '_' (example 112553)	Remove detail before '_' (example 112553)

Thanks..

Anil_Babu_Samineni — Mon, 18 Dec 2017 13:06:00 GMT

What are the C,F,H ??

May be look SubField() function ?

nareshthavidishetty — Mon, 18 Dec 2017 13:10:13 GMT

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 — Mon, 18 Dec 2017 13:13:52 GMT

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 — Mon, 18 Dec 2017 13:16:55 GMT

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

Anonymous — Mon, 18 Dec 2017 13:17:58 GMT

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