load script

[mambi]

hi everyone,

from the table below

category 1

sub category 1.1

sub category 1.2

sub category 1.3

category 2

sub category 2.1

sub category 2.2

i want to load my data so i get this results :

category 1	sub category 1.1
category 1	sub category 1.2
category 1	sub category 1.3
category 2	sub category 2.1
category 2	sub category 2.2

thanks

[Nicole-Smith]

Load script like the following should do the trick (example file also attached):

DataTemp:

LOAD * INLINE [

    Category

    category 1

    sub category 1.1

    sub category 1.2

    sub category 1.3

    category 2

    sub category 2.1

    sub category 2.2

];

Data:

LOAD Category,

    SubField(Category, 'category ', 2) as CategoryNum

RESIDENT DataTemp

WHERE NOT Category like 'sub*';

LEFT JOIN (Data)

LOAD Category as [Sub Category],

    SubField(SubField(Category, 'sub category ', 2), '.', 1) as CategoryNum

RESIDENT DataTemp

WHERE Category like 'sub*';

DROP TABLE DataTemp;

DROP FIELD CategoryNum;

[Gysbert_Wassenaar · Accepted solution]

Temp:
LOAD *
  , if(left(F1,3)='sub',peek(Category),F1) as Category
  , if(left(F1,3)='sub',F1) as SubCategory
INLINE [
    F1
    category 1
    sub category 1.1
    sub category 1.2
    sub category 1.3
    category 2
    sub category 2.1
    sub category 2.2
];

Result:
LOAD Category, SubCategory
RESIDENT Temp
where len(trim(SubCategory));

DROP TABLE Temp;

[mambi]

sorry i changed a little my schema as :

Sport

foot

tenis

basketball

travel

France

spain

to get this result :

sport	foot
sport	tenis
sport	basketball
travel	France
travel	spain

[Gysbert_Wassenaar]

replace

     left(F1,3)='sub'

with 

     not match(F1,'Sport','travel')