split one field into many i.e subfield delimited etc

[#1]

Hi

I have a field which shows all "ProblemTypes" we log maintainance work against.  Although the problem types are are stored in a single field they actually belong in a hierarchy which is shown in the "HierarchyTrace" field from the same table.  Both fields are pulled in from a SQL data base into my QV model via QVD loader file.

What I'd like to do (in the loader script) is to split out the "HierarchyTrace" field, into the various hierarchy levels for the problem types.

So for example we could have a problems type of:

"Electrical"

"Light"

"Light-Fail"

"Light-Damage"

The last 3 are sub types of the "Electrical" problem type, in fact the 2nd one would be the 2nd level of the hierarchy, the 3rd & 4th items in this example would both be in the 3rd level of the hierarchy.

The "HierarchyTrace" field lists this as follows (for example):

Electrical|

Electrical|Light|

Electrical|Light|Light-Fail|

Electrical|Light|Light-Damage|

Notice the | character  is the delimiter!

So what I want to create would be for example:

ProblemType	HierarchyTrace	HierachyLevel1	HierachyLevel2	HierachyLevel3
Electrical	Electrical|	Electrical	-	-
Light	Electrical|Light|	Electrical	Light	-
Light-Fail	Electrical|Light|Light-Fail|	Electrical	Light	Light-Fail
Light-Damage	Electrical|Light|Light-Damage|	Electrical	Light	Light-Damage

I'm not sure best way to go about doing this, I'm guessing it may be using subfield, loops etc???

Attached are sample of the qvw with the fields mentioned above and excel file showing how I've used the delimiter to split the "HierarchyTrace" field into 3 columns etc

any help would be great, thanks

Dan

[#2 · Accepted solution]

Hi Dan,

you have already come up with the solution in using subfield, you just need to expand that now to create the fields you require, from the right subfield position you need

SubField([Hierarchy Trace],'|',1) AS Hierarchy_Level1
,SubField([Hierarchy Trace],'|',2) AS Hierarchy_Level2
,SubField([Hierarchy Trace],'|',3) AS Hierarchy_Level3

hope that helps

Joe

[#3]

i was thinking it'll be more complex than that but happy simple solution worked

just need to add quotes like  '|' around the delimiter character!

Dan

[#4]

whoops how did I miss that there! Have edited now for anyone else reading

glad to help