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

loading data

hi, everyone

i've this type of data (structured with different spaces at the beginning)

Category 1

xxx

   Sub Category 1

xxx

     Sub Sub Category 1

xxx

       Price Product 1

120.26

         Product 1

xxx

           Characteristic 1 P1

xxx

           Characteristic 2 P1

xxx

       Price Product 2

202.86

         Product 2

xxx

           Characteristic 1 P2

xxx

           Characteristic 2 P2

xxx

and i want to get this result :

Category 1

Sub Category 1

Sub Sub Category 1

Product 1

Characteristic 1 P1

Price Product 1

120.26

Category 1

Sub Category 1

Sub Sub Category 1

Product 1

Characteristic 1 P2

Price Product 1

120.26

Category 1

Sub Category 1

Sub Sub Category 1

Product 2

Characteristic 2 P1

Price Product 2

202.86

Category 1

Sub Category 1

Sub Sub Category 1

Product 2

Characteristic 2 P2

Price Product 2

202.86

any idea ?

Tags (2)
8 Replies
datanibbler
Esteemed Contributor

Re: loading data

Hi Mambi,

what I usually do when I have something like this is the following (not the shortest possibility, but I think it works best):

(you might have to turn/ transpose your base_table first to get to the starting point, so that you have a lot of columns, but only two rows - the second row being the one with the numbers - you might also have to "populate through" the numbers to get rid of those 'XXX' thingys)

- I load it all at first, regardless of any structure (maybe taking out the Blanks by using TRIM would be an idea)

- Then I have a nr. of RESIDENT LOADs, one for every dimension_field I want (that would probably be three in your

  case, 'Category', 'Sub Category' and 'Sub Sub Category')

=> In every one of these RESIDENT LOADs (which must all be concatenated, but that shouldn't be

      an issue), I hold all of the dimensions static to one of their possible values  and load only the data_field.

=> Involves some careful counting of course to make sure you get all values of every dimension.

=> That way I end up with the maximum number of dimension_fields which gives me every possibility to make my chart extremely flexible.

HTH

Best regards,

DataNibbler

engishfaque
Valued Contributor III

Re: loading data

Dear Mambi,

You can do it through Table Box.

Steps

New sheet objects > Table Box > Add your "Available Fields" > Apply > OK.

Kind regards,

Ishfaque Ahmed

mambi
Contributor III

Re: loading data

it's not possible because i've only 2 fields

Re: loading data

Hi,

interesting question.

I would try to create a hierarchy table using the hierarchy prefix for loads.

Here is my approach:

QlikCommunity_Thread_125041_Pic2.png

expression:

=If(levelName='Characteristic',Sum(TOTAL <Category, [Sub Category], [Sub Sub Category], [Price Product]> If(levelName='Price Product',value)))

QlikCommunity_Thread_125041_Pic1.png

used script:

Set Verbatim = 1;

mapLevelNames:

Mapping LOAD * Inline [

old,new

level1,Category

level2,Sub Category

level3,Sub Sub Category

level4,Price Product

level5,Product

level6,Characteristic

];

tabInput:

LOAD @1 as levelSource,

    @2 as value,

    (Len(@1)-Len(LTrim(@1))+1)/2 as levelNo, //adjust according to level indent

    RecNo() as ID

FROM [http://community.qlik.com/thread/125041]

(html, codepage is 1252, no labels, table is @1);

tabTemp:

LOAD levelNo as lvlNo1, ID as ID1 Resident tabInput;

Join

LOAD levelNo as lvlNo2, ID as ID2 Resident tabInput;

Left Join (tabInput)

LOAD ID1 as ID,

    max(ID2) as parentID

Resident tabTemp

Where lvlNo2=lvlNo1-1 and ID2<ID1

Group By ID1;

DROP Table tabTemp;

tabOutput:

Hierarchy (ID, parentID, level,,, ProductHierarchy,, levelNo)

LOAD ID,

    parentID,

    Trim(levelSource) as level,

    levelSource,

    value

Resident tabInput;

DROP Table tabInput;

Left Join (tabOutput)

LOAD Distinct

  levelNo,

  ApplyMap('mapLevelNames', 'level'&levelNo) as levelName

Resident tabOutput;

Rename Fields using mapLevelNames;

hope this helps

regars

Marco

martynlloyd
Contributor III

Re: loading data

This will index the rows for you:

SET VERBATIM = 1;

Loading your data here
Struc:
LOAD Cat
FROM
[..\Community.xlsx]
(ooxml, embedded labels, table is Sheet1);


Levels:
LOAD
len(Text(Cat)) - len(Ltrim(Cat)) as Level
,Text(Cat)
Resident Struc;

SET VERBATIM = 0;

If you need help putting the result into another table, let us know.

BTW, didn't work with an inline load

Regards,

Marty.

mambi
Contributor III

Re: loading data

Hi, thanks for your help,

your solution worked for me but only if i load les than 2000 rows ( my file contain 20000 rows)

any idea ?

Re: Re: loading data

Hi,

late reply, but better late than never ...

I tried to calculate the parentID without using a cartesian product (table tabTemp) because I guess this caused the performance issue.

Here is my result:

Set Verbatim = 1;

mapLevelNames:

Mapping LOAD * Inline [

    old, new

    level1,Category

    level2,Sub Category

    level3,Sub Sub Category

    level4,Price Product

    level5,Product

    level6,Characteristic

];

tabInput:

LOAD *, SubField(IDPath,'/',levelNo-1) as parentID;

LOAD *, Left(Peek(IDPath)&'/',Index(Peek(IDPath)&'/','/',levelNo-1))&ID as IDPath;

LOAD @1 as levelSource,

    @2 as value,

    (Len(@1)-Len(LTrim(@1))+1)/2 as levelNo, //adjust according to level indent

    RecNo() as ID

FROM [http://community.qlik.com/thread/125041]

(html, codepage is 1252, no labels, table is @1);

tabOutput:

Hierarchy (ID, parentID, level,,, ProductHierarchy,, levelNo)

LOAD ID,

    parentID,

    Trim(levelSource) as level,

    levelSource,

    value

Resident tabInput;

DROP Table tabInput;

Left Join (tabOutput)

LOAD Distinct

  levelNo,

  ApplyMap('mapLevelNames', 'level'&levelNo) as levelName

Resident tabOutput;

Rename Fields using mapLevelNames;

please try this solution and reply if it's working better.

thanks

regards

Marco

mambi
Contributor III

Re: Re: loading data

Hi and Happy New Year,

i'm back with my new issue but this time i got this type of data :

Header 1Price
Category 1xxx
  Sub Category 1xxx
    Sub Sub Category 1xxx
      Price Product 1120.26
        Characteristic 1 P1xxx
          Product 1xxx
        Characteristic 2 P1xxx
          Product 1xxx
     Price Product 2155
        Characteristic 1 P2xxx
          Product 2xxx
        Characteristic 2 P2xxx
          Product 2xxx

any idea to get this result :

Category 1

Sub Category 1

Sub Sub Category 1

Product 1

Characteristic 1 P1

Price Product 1

120.26

Category 1

Sub Category 1

Sub Sub Category 1

Product 1

Characteristic 1 P2

Price Product 1

120.26

Category 1

Sub Category 1

Sub Sub Category 1

Product 2

Characteristic 2 P1

Price Product 2

202.86

Category 1

Sub Category 1

Sub Sub Category 1

Product 2

Characteristic 2 P2

Price Product 2

202.86

thanks for your help.