4 Replies Latest reply: Sep 18, 2016 8:06 PM by Marco Wedel RSS

    How to identify childs and create groups for the given data

    Ravi Kumar

      Hi All,

       

      I am looking for a solution for the sample data which i am posting here.

       

      Please find the attachment for the data and requirements.

       

      Below is the screenshot for the same.(Go through the attachment for complete details)

      Screen.png

       

      Thanks in advance.

       

      Ravikumar

        • Re: How to identify childs and create groups for the given data
          Stefan Wühl

          I haven't fully understood your description, aren't 'T' items missing in your M1 material list?

           

          Looks like you are coping with a kind of BOM, so maybe have a look at

           

          Bill of Materials

          • Re: How to identify childs and create groups for the given data
            Hirish V

            Hi,

            Look into this,

             

            Temp:
            LOAD 
            IF(Left(Item,1) ='M',Item) as ItemGrouping,
            Item,
            Qty
            FROM
            [Item_Data_Sample Grouping-233096.xlsx]
            (ooxml, embedded labels, header is 1 lines, table is Sheet1);
            
            
            Store Temp into Data.qvd;
            
            
            Drop Table Temp;
            
            
            QVD:
            LOAD @1 as ItemGrouping, 
                 @2 as Item, 
                 @3 as Qty,
                 IF(@2=@1,If(@2=@1 and @3>=1,0,1)) as CreateFlag 
            FROM
            [Data.qvd]
            (qvd, filters(
            Replace(1, top, StrCnd(null)),
            ColXtr(1, RowCnd(CellValue, 1, StrCnd(null)), 1),
            ColXtr(1, RowCnd(CellValue, 1, StrCnd(null)), 1), 
            Replace(4, top, StrCnd(null)),
            Replace(1, top, StrCnd(null))
            ));
            
            
            Data:
            Load *,
              if(isnull(CreateFlag),Peek('Flag'),CreateFlag) as Flag
              Resident QVD;
              Drop Table QVD;
            
            

             

            Using above Script and by creating a pivot table at front -end,

            Item_Data_Sample Grouping-233096.PNG

             

            HTH,

            PFA,

            Hirish

            • Re: How to identify childs and create groups for the given data
              Marco Wedel

              Hi,

               

              maybe one solution might be also:

               

              QlikCommunity_Thread_233096_Pic1.JPG

               

              QlikCommunity_Thread_233096_Pic2.JPG

               

               

              mapLevel:
              Mapping
              LOAD Letter, RecNo() INLINE [
                  Letter
                  F
                  M
                  T
                  A
                  D
                  W
              ];
              
              table1:
              LOAD *,
                  If(Level>1,RangeSum(Peek(Group),-(Item like 'M*'))) as Group,
                  If(Level>1,If(Item like 'M*',-(Qty=0),Peek(Flag))) as Flag,
                  If(Level>Previous(Level),Peek(Path)&Repeat('/',Level-Previous(Level))&Item,If(Level=1,Item,Left(Peek(Path),Index(Peek(Path),'/',Level-1))&Item)) as Path;
              LOAD RecNo() as ID,
                  Item,
                  ApplyMap('mapLevel',Left(Item,1)) as Level,
                  Qty
              FROM [https://community.qlik.com/servlet/JiveServlet/download/1125363-245898/Item_Data_Sample.xlsx] (ooxml, embedded labels, header is 1 lines, table is Sheet1);
              

               

              hope this helps

               

              regards

               

              Marco