how to make a hierarchy using numbers

[qluser01]

Hi!

I have an input like this:

number	name
1	Process_name
1.1	SubProcess_name
1.1.1	Step_name
1.1.1.1	Substep_name
1.1.1.2	Substep_2_name
1.1.1.3	Substep3_name
1.1.2	Step2_name
1.1.2.1	Substep4_name
1.1.2.2	Substep5_name

And I need to make a table inside QView via script as follows:

number	Process	SubProcess	Step	SubStep
1.1.1.1	Process_name	SubProcess_name	Step_name	Substep_name
1.1.1.2	Process_name	SubProcess_name	Step_name	Substep_2_name
1.1.1.3	Process_name	SubProcess_name	Step_name	Substep3_name
1.1.2.1	Process_name	SubProcess_name	Step2_name	Substep4_name
1.1.2.2	Process_name	SubProcess_name	Step2_name	Substep5_name

So first digit - is Process numberm there are more than 10 processes - number from 1. to 10.

2nd digit - SubProcess number

and so on...

for your convenience:

Load * Inline

[number, name

1, Process_name

1.1, SubProcess_name

1.1.1, Step_name

1.1.1.1, Substep_name

1.1.1.2, Substep_2_name

1.1.1.3, Substep3_name

1.1.2, Step2_name

1.1.2.1, Substep4_name

1.1.2.2, Substep5_name

2, Process2_name

2.1, SubProcess2_name

2.1.1, Step222_name

2.1.1.1, Substep222_name

2.1.1.2, Substep223_name

];

Also, I need 2 solutions:

1) if we have hierarchy from Process to SubStep (see example above)

2) if we have hierarchy from Process to Step  - so no SubSteps present in initial table at all

[rahulpawarb · Accepted solution]

Hello Eeacrw,

Please refer below given sample script:

Data:
LOAD * INLINE [
number, name
1, Process_name
1.1, SubProcess_name
1.1.1, Step_name
1.1.1.1, Substep_name
1.1.1.2, Substep_2_name
1.1.1.3, Substep3_name
1.1.2, Step2_name
1.1.2.1, Substep4_name
1.1.2.2, Substep5_name
2, Process2_name
2.1, SubProcess2_name
2.1.1, Step222_name
2.1.1.1, Substep222_name
2.1.1.2, Substep223_name
];

TmpData:
LOAD number,
     Left(number,5) AS Number1,
     Left(number,3) AS Number2,
     Left(number,1) AS Number3,
     name AS SubStep
Resident Data
Where Len(number) = 7;
JOIN (TmpData)
LOAD Left(number,1) AS Number3,
     name AS Process
Resident Data
Where Len(number) = 1;
JOIN (TmpData)
LOAD Left(number,3) AS Number2,
     name AS SubProcess
Resident Data
Where Len(number) = 3;
JOIN (TmpData)
LOAD Left(number,5) AS Number1,
     name AS Step
Resident Data
Where Len(number) = 5;

FinalData:
LOAD number,
     Process,
     SubProcess,
     Step,
     SubStep
Resident TmpData;

DROP Tables Data, TmpData;

Also refer the attached sample application.

Hope this will be helpful.

Regards!

Rahul