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

Adding detail dynamically to field

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.

Capture.PNG

Thanks..

17 Replies
supriyabiware
Creator
Creator

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

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

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

Capture.PNG

What would be the Product_ID for these guys?

its_anandrjs

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

YoussefBelloum
Champion
Champion

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

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$);