Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mambi
Contributor III

load script

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 1sub category 1.1
category 1sub category 1.2
category 1sub category 1.3
category 2sub category 2.1
category 2sub category 2.2

thanks

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: load script

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;


talk is cheap, supply exceeds demand
4 Replies

Re: load script

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;

MVP & Luminary
MVP & Luminary

Re: load script

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;


talk is cheap, supply exceeds demand
mambi
Contributor III

Re: load script

sorry i changed a little my schema as :

Sport
foot
tenis
basketball
travel
France
spain

to get this result :

sportfoot
sporttenis
sportbasketball
travelFrance
travelspain
MVP & Luminary
MVP & Luminary

Re: load script

replace

     left(F1,3)='sub'

with 

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


talk is cheap, supply exceeds demand