Adding detail dynamically to field

[nareshthavidishetty]

Hi,

We are having two diffrent fileds like Product_ID and Product_Num like(See attached spreadsheet).

We need to get Product_ID in the format of Product_Num.

Thanks..

[supriyabiware]

can you explain in detail? the question seems incomplete.

just a guess - you can use substring and Concatenate the headers to product ID to get it look like product num.

Thanks

Supriya

[nareshthavidishetty]

Hi ,

I mean the same level of detail from Product_Num need in Product_ID.

Both the fields are from different tables.

To join those tables I need to create a key based on Product_Num.

If we get Product_Id as Product_Num the level of detail is not good and the subset ration would be zero.

Because of that I need Product_Id as sames as Product_Num.

Thanks..

[YoussefBelloum]

stalwar1‌ I was thinking about the KeepChar for him, but it will not do the trick here

[sunny_talwar]

May be like this

Num(SubField(Product_Num, '_', -1), '0000000000') as Product_ID

[sunny_talwar]

The only thing I am not sure about is this list of Product_Num

What would be the Product_ID for these guys?

[its_anandrjs]

Peroduct_Num or ProductId do you need let me clear for this.

[YoussefBelloum]

the idea here, is to extract the chars present on both columns..

example:

Column1: 900071407Q Column2: H1_900071407Q  the result should be: 900071407Q

Column1: 0000112552 Column2: C0_112552 the result should be: 112552

i was trying with wildmatch with '*' and keepchar. but it doesn't do the trick

it would be very interesting to be able to do this

[YoussefBelloum]

because on his entire list, there is matching chars on all the lines between both columns.

i already flag it, but i was not able to extract that matching values..

[sunny_talwar]

I think the id is to be able to join the two fields from the two table, right? All we need to do is to manipulate Product_Num to look like Product_ID so that the two tables can join. If that is the case, what I have creates Product_ID (except for the few), which can not be used for joining purposes. To address the rest, OP can try this

Table:

LOAD Product_Num,

If(IsNull(Num(SubField(Product_Num, '_', -1), '0000000000')), SubField(Product_Num, '_', -1), Num(SubField(Product_Num, '_', -1), '0000000000')) as Product_ID

FROM

[..\..\Downloads\CH25_20171213_073627.xls]

(biff, embedded labels, table is Sheet1$);