Extract some part of data

[lakshmijanaki22]

Hi I have a field like below

Number	Title
1.1	abc
1.1.1	def
1.1.2	sss
1.1.3	ddd
1.1.1.1	ppp
1.1.1.2	hhh
1.2	aaa

I want to create hierarchy like 1 in level 1, level 2 1.1,1.2, level 3 1.1.1,1.1.2,1.1.3 and so on. while showing hierarchy in graph I need to show title instead if number. Please suggest.

[Zhandos_Shotan]

Hi!

You need to create ParentID field and use hierarchy load.

To create ParentID use logic like this:

Left(Number, SubStringCount(Number, '.')+SubStringCount(Number, '.')-1) as ParentID 

It works only if you levels in 1 to 9 except last level.

For levels >=10 need to use more complex logic using Subfield(), Mid() and another string functions.

Or alternate way:

Use SubField function for create dimension for each level.

Load

*,

SubField(Number, '.', 1) as Level1,

SubField(Number, '.', 2) as Level2,

SubField(Number, '.', 3) as Level3, etc.

Hope it helps

[mahaveerbiraj]

Hi laksmi ,

folow below steps it may help you :

table:
load * Inline [
Number, Title
1.1 ,abc
1.1.1,def
1.1.2,sss
1.1.3,ddd
1.1.1.1,ppp
1.1.1.2,hhh
1.2,aaa
1,sdf
];

table2:
load *,
'level '&((Len(Number)/2)+.5) as Level
Resident table;
Drop Table table;

table3:
Generic
load
Number,
Level,
Title

Resident table2;

Drop Table table2;