topic Re: Adding detail dynamically to field in QlikView

Adding detail dynamically to field

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

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

Re: Adding detail dynamically to field

supriyabiware — Wed, 13 Dec 2017 08:37:53 GMT

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

Re: Adding detail dynamically to field

nareshthavidishetty — Wed, 13 Dec 2017 09:46:55 GMT

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

Re: Adding detail dynamically to field

YoussefBelloum — Wed, 13 Dec 2017 13:40:20 GMT

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

Re: Adding detail dynamically to field

sunny_talwar — Wed, 13 Dec 2017 13:42:22 GMT

May be like this

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

Re: Adding detail dynamically to field

sunny_talwar — Wed, 13 Dec 2017 13:45:03 GMT

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

What would be the Product_ID for these guys?

Re: Adding detail dynamically to field

its_anandrjs — Wed, 13 Dec 2017 13:48:51 GMT

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

Re: Adding detail dynamically to field

YoussefBelloum — Wed, 13 Dec 2017 13:52:33 GMT

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

Re: Adding detail dynamically to field

YoussefBelloum — Wed, 13 Dec 2017 13:58:15 GMT

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

Re: Adding detail dynamically to field

sunny_talwar — Wed, 13 Dec 2017 14:06:35 GMT

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

Re: Adding detail dynamically to field

YoussefBelloum — Wed, 13 Dec 2017 14:16:38 GMT

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

Re: Adding detail dynamically to field

sunny_talwar — Wed, 13 Dec 2017 14:21:23 GMT

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?

Re: Adding detail dynamically to field

YoussefBelloum — Wed, 13 Dec 2017 14:29:31 GMT

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

Re: Adding detail dynamically to field

sunny_talwar — Wed, 13 Dec 2017 14:32:29 GMT

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

Re: Adding detail dynamically to field

YoussefBelloum — Wed, 13 Dec 2017 14:35:17 GMT

Thank you !

Re: Adding detail dynamically to field

nareshthavidishetty — Wed, 13 Dec 2017 15:48:29 GMT

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

Re: Adding detail dynamically to field

MarcoWedel — Wed, 13 Dec 2017 23:57:44 GMT

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

Re: Adding detail dynamically to field

nareshthavidishetty — Tue, 19 Dec 2017 06:45:07 GMT

Hi,

We can join the tables but data is not associated.

Thanks..