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

[YoussefBelloum]

Ok, but here yo just did the opposite of what he is asking,

Actually, he need to get Product_ID in the format of Product_Num.

example:

0000112552 ==> 112552

you did this:

H5_112552 ==> 0000112552

[sunny_talwar]

I am not 100% sure what OP needs, but this is what I understand

There are two tables, one has Product_ID and one has Product_Num... in order to join them, they need to have a common dimension.... but I can never go from Product_ID to Product_Num because I don't know what prefix I can add, but I can go from Product_Num to Product_ID.

If that is not what you understand, then can you explain what you think OP is looking to get? a common field from both Product_ID and Product_Num? For the above example you shared, would the new field have value 112552?

[YoussefBelloum]

you are right. I'm just making things more complicated, to join the tables, your solution is enough.

but I'm going to take the opportunity, to ask this question:

is there a way to extract a string of the identical characters present between 2 fields (on the same line) ??

Example:

line1:

Column1: 0930002090 Column2: H1_930002090 ==> Result: 930002090

line2:

Column1: ICN9A Column2: H2_ICN9A ==> Result: ICN9A

I mean the exact char (not like removing zeros inside or something..)

[sunny_talwar]

I think marcowedel‌ might be the best guy to answer this question. He does wonders with these kind of tasks.

[YoussefBelloum]

Thank you !

[nareshthavidishetty]

Hi,

Sorry for the delay .

We need the output like below

Product_Id:0930002090

Product_num: H1_930002090

==> Result Product_Id  as: H1_930002090

Thanks..

[MarcoWedel]

Hi,

maybe one solution could be:

table1:
LOAD AutoNumber(SubField(Product_Num,'_',-1),'%Key') as %Key,
    Product_Num
FROM [https://community.qlik.com/servlet/JiveServlet/download/1400250-307329/CH25_20171213_073627.xls] (biff, embedded labels, table is Sheet1$);

table2:
LOAD AutoNumber(Mid(Product_ID,FindOneOf(Product_ID,PurgeChar(Product_ID,'0'))),'%Key') as %Key,
    Text(Product_ID) as Product_ID
FROM [https://community.qlik.com/servlet/JiveServlet/download/1400250-307329/CH25_20171213_073627.xls] (biff, embedded labels, table is Sheet1$);

hope this helps

regards

Marco

[nareshthavidishetty]

Hi,

We can join the tables but data is not associated.

Thanks..