8 Replies Latest reply: Jan 1, 2015 9:52 AM by Mambi Badi RSS

    loading data

    Mambi Badi

      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 ?

        • Re: loading data
          Friedrich Hofmann

          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

          • Re: loading data
            ISHFAQUE AHMED

            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

            • Re: loading data
              Marco Wedel

              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

                • Re: loading data
                  Mambi Badi

                  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
                      Marco Wedel

                      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

                        • Re: Re: loading data
                          Mambi Badi

                          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.

                    • Re: loading data
                      Martyn Lloyd

                      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.