loading data

[mambi]

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 ?

[datanibbler]

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]

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]

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

[MarcoWedel]

Hi,

interesting question.

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

Here is my approach:

expression:

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

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]

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]

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 ?

[MarcoWedel]

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]

Hi and Happy New Year,

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

Header 1	Price
Category 1	xxx
Sub Category 1	xxx
Sub Sub Category 1	xxx
Price Product 1	120.26
Characteristic 1 P1	xxx
Product 1	xxx
Characteristic 2 P1	xxx
Product 1	xxx
Price Product 2	155
Characteristic 1 P2	xxx
Product 2	xxx
Characteristic 2 P2	xxx
Product 2	xxx

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.