Subfield() output filtering in Qlik Sense Script

[Rubio]

Hi Guys;

I have a table field with concatenated values like this : object_id<separator>Number<separator>object_id...

An example : 1d4f411c-490a-4eda-bfbc-d74ea3f0193b;#54;#4358592a-b376-429c-bcef-2eaf841dd2b5;#66;#28ba4f9b-93ce-4d48-9814-dcbb037d99f4;#67

I'd like to have only the objects id in a column.

I tried :  IF(len(trim(SubField([My Field], ';#')))>10, SubField([My Field], ';#')) as [My Column]. But it gives me null values and numbers values as well as the objects  ids.

Any help would be appreciated. I untill now could always just use charts functions but as far as the program evolves I need script loaded tables.

Thank you.

[rwunderlich]

LOAD
*
Where IsNum(ObjectId)
;
LOAD
SubField(Infield, ';#') as ObjectId
Inline [
Infield
1d4f411c-490a-4eda-bfbc-d74ea3f0193b;#54;#4358592a-b376-429c-bcef-2eaf841dd2b5;#66;#28ba4f9b-93ce-4d48-9814-dcbb037d99f4;#67
9999999-490a-4eda-bfbc-d74ea3f0193b;#99
]
;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[Rubio]

Hi Rob;

Thank you for taking care of my request.

Could you explain what you are doing in the two steps ? What's "LOAD * Where IsNum (ObjectId)" for ?

Thanks !

[kaushiknsolanki]

Hi,

Its a where clause, which states that load only the data where the ObjectID is numeric.

Regards,

Kaushik Solanki

[Rubio]

Thank you Kaushik;

I'will explain more from my side.

Below is my in field that I read from an Excel file. I am looking for an output that gives me in one column all the Objects_ID like "8b1c34a8-96ff-4464-aab9-62a382909ea6" without the numbers and the separators. Multiple occurrences of object_IDs should be kept also (Please see 2nd table below).

InField

8b1c34a8-96ff-4464-aab9-62a382909ea6;#35

b0c25a57-01cf-4d5f-a7d8-561b5fa61588;#282

0120b3c9-c129-473a-9fc8-e06bf051ecfe;#121

166fcb9a-4dc4-4345-a701-9effa15bd197;#100

faead773-0eef-4a67-acb1-9c9c6662d65b;#102;#b51242eb-caaf-4ca3-8e8b-c0c93869acea;#113;#4413360e-87a5-4e09-984f-87fc9954bb02;#86

5859d385-e640-4245-817e-9546874383e8;#278;#011b9636-c11b-406f-9679-7a20ea371d78;#244;#7ef381f7-5815-4054-8535-c955ff9deeb3;#248;#5fd217f4-3e73-4efa-a832-705280479a1d;#253;#aaf5b708-d60e-487b-baaf-120df52f7145;#261;#69ff2901-0680-4835-b401-fa4aa9dad185;#247

011b9636-c11b-406f-9679-7a20ea371d78;#244;#69ff2901-0680-4835-b401-fa4aa9dad185;#247

011b9636-c11b-406f-9679-7a20ea371d78;#244

Expected output:

Object_IDs
8b1c34a8-96ff-4464-aab9-62a382909ea6
b0c25a57-01cf-4d5f-a7d8-561b5fa61588
0120b3c9-c129-473a-9fc8-e06bf051ecfe
166fcb9a-4dc4-4345-a701-9effa15bd197
faead773-0eef-4a67-acb1-9c9c6662d65b
b51242eb-caaf-4ca3-8e8b-c0c93869acea
4413360e-87a5-4e09-984f-87fc9954bb02
5859d385-e640-4245-817e-9546874383e8
011b9636-c11b-406f-9679-7a20ea371d78
7ef381f7-5815-4054-8535-c955ff9deeb3
5fd217f4-3e73-4efa-a832-705280479a1d
aaf5b708-d60e-487b-baaf-120df52f7145
69ff2901-0680-4835-b401-fa4aa9dad185
011b9636-c11b-406f-9679-7a20ea371d78
69ff2901-0680-4835-b401-fa4aa9dad185
011b9636-c11b-406f-9679-7a20ea371d78

Thanks !

[Anil_Babu_Samineni · Accepted solution]

Seems working what @rwunderlich provided. But one correction may be needed

Load * Where Not IsNum(Object_IDs);

Load SubField(InField,';#') as Object_IDs Inline [

InField

8b1c34a8-96ff-4464-aab9-62a382909ea6;#35

b0c25a57-01cf-4d5f-a7d8-561b5fa61588;#282

0120b3c9-c129-473a-9fc8-e06bf051ecfe;#121

166fcb9a-4dc4-4345-a701-9effa15bd197;#100

faead773-0eef-4a67-acb1-9c9c6662d65b;#102;#b51242eb-caaf-4ca3-8e8b-c0c93869acea;#113;#4413360e-87a5-4e09-984f-87fc9954bb02;#86

5859d385-e640-4245-817e-9546874383e8;#278;#011b9636-c11b-406f-9679-7a20ea371d78;#244;#7ef381f7-5815-4054-8535-c955ff9deeb3;#248;#5fd217f4-3e73-4efa-a832-705280479a1d;#253;#aaf5b708-d60e-487b-baaf-120df52f7145;#261;#69ff2901-0680-4835-b401-fa4aa9dad185;#247

011b9636-c11b-406f-9679-7a20ea371d78;#244;#69ff2901-0680-4835-b401-fa4aa9dad185;#247

011b9636-c11b-406f-9679-7a20ea371d78;#244

];

[Rubio]

It worked 😊! Thank you Rob, Anil for your support.