topic Re: Subfield not working in QlikView

Subfield not working

ABNoel_Travelers — Thu, 18 Mar 2021 19:14:47 GMT

Afternoon.. I have a strange one I can't seem to get working. I have a data field in a table that contains multiple fields I am attempting to break out into a new table. The data string looks like this:

{ClusterId:0312-184434-12345,Environment:dev,LOB:pi,ClusterName:Quickstart,Data Classification:confidential,Creator:xxxxx,Vendor:Databricks,Project:pi-ai,Owner 1:XYZ,Owner 2:XYZ2,System Number:####}

The above value is loaded into a temp table inside of Qlik with other fields from the entire string first and then it is broken down into a temp table that does break it down to the value in the string (ClusterId:0312-184434-12345), which I use subfield for, but when I try to SubField out the string into a new table and break the field to the true value in a table the process is only grabbing the first field value(0312-184434-12345) and none of the others.

/* Initial code pass breaking the string by "," that works */

RawTags:

LOAD
PurgeChar(workspaceId, Chr(34)) as workspaceId,
PurgeChar(SubField([tags],',',1),Chr(34)) as ClusterId,
PurgeChar(SubField([tags],',',2),Chr(34)) as Environment,
PurgeChar(SubField([tags],',',3),Chr(34)) as LOB,
PurgeChar(SubField([tags],',',4),Chr(34)) as ClusterName,
PurgeChar(SubField([tags],',',5),Chr(34)) as DataClassification,
PurgeChar(SubField([tags],',',6),Chr(34)) as Creator,
PurgeChar(SubField([tags],',',7),Chr(34)) as Vendor,
PurgeChar(SubField([tags],',',8),Chr(34)) as Project,
PurgeChar(SubField([tags],',',9),Chr(34)) as Owner1,
PurgeChar(SubField([tags],',',10),Chr(34)) as Owner2,
PurgeChar(SubField([tags],',',11),Chr(34)) as SystemNumber
FROM [file.qvd]
(qvd);

/* This isn't working */

CleanTags:

LOAD
workspaceId,
SubField([ClusterId],':',2) as [ClusterID_P],
SubField([Environment],':',2) as [Environment_P],
SubField([LOB],':',2) as [LOB_P],
SubField([ClusterName],':',2) as [ClusterName_P],
SubField([DataClassification],':',2) as [DataClassification_P],
SubField([Creator],':',2) as [Creator_P],
SubField([Vendor],':',2) as [Vendor_P],
SubField([Project],':',2) as [Project_P],
SubField([Owner1],':',2) as [Owner1_P],
SubField([Owner2],':',2) as [Owner2_P],
SubField([SystemNumber],':',2) as [SystemNumber_P]
Resident [RawTags];

What am I missing?

Re: Subfield not working

MK9885 — Thu, 18 Mar 2021 20:43:29 GMT

What is the original string in a field?

what do you want, can you break it down?
Ex: numbers separate then alphabets separate or mix or pick on 4th number etc

Re: Subfield not working

rwunderlich — Thu, 18 Mar 2021 21:46:27 GMT

Your script as posted works for me. I get all the values for every field. Don't know what may be going wrong with your real run.

As an alternative, I usually approach this type of key:value data with the same general purpose preceding load script so I don't have to deal with typing fieldname. For example, this is what I use.

Data:
// Generic Load to transpose
// Key to Field
Generic LOAD
RecId, Key, Value
;
//Separate key & value
LOAD
RecId,
trim(subfield(Pair,':',1)) as Key,
trim(subfield(Pair,':',2)) as Value
;
//Break out each key:value pair
LOAD
RecId,
subfield(Input,',') as Pair
;
//Load the raw Input
LOAD *, RecNo() as RecId
INLINE [
Input
Name:Shoes, Size:L, Color:Blue Suede, Stock: 200
Name:Socks, Model:Mens Casual, Stock:0, Color:Black
Name:Pants, Error: No attributes found
] (delimiter is '|')
;

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