6 Replies Latest reply: Aug 28, 2014 9:22 AM by Marco Wedel RSS

    try to nest a subfield loop

      hello I'm trying to load this table in order to group values according to an established order.

      the data in two columns separated by comma


      IDPad
      Stand
      011,2  
      H11, G21
      021,1
      D1, S31
      031,1,3D4, F1 , E12


      the first Pad matches the first of the Stand column and so on..


      The best result on loading data should be


      IDPad& Stand
      01Pad. 1 Stand. H11 - Pad 2 Stand G21
      02Pad. 1 Stand. D1 S31 
      03Pad. Stand. D4 F1 - Pad. Stand. E12


      As now I'm only get this

       

      IDPadStand
      011H11
      012G21
      021D1
      021S31

      ..and so on

       

      this is tehe load script that i'm using to try to get the result:

       

      for i=1 to 10

       

      LOAD ID,

       

           subfield(purgechar(Pad, chr(10)),',',$(i)) as Pad,

           subfield(purgechar(Stand, chr(10)),',',$(i))  as Stand,

       

      FROM

      [C:\Users\silvas\Desktop\EMP -elabora dati\EICMA\catalogo_completo_25.xlsx]

      (ooxml, embedded labels, table is Recuperati_Foglio1);

       

      NEXT i;

       

      Someone could help me on this?

       

      thnks

      Stefano

        • Re: try to nest a subfield loop
          Marco Wedel

          try with subfield(), IterNo() and Concat()

            • Re: Re: try to nest a subfield loop
              Marco Wedel

              not exactly, but maybe helps:

               

              QlikCommunity_Thread_130987_Pic1.JPG.jpg

               

              LOAD ID,
                  Concat('Pad. '&Pad&' Stand. '&Stand, ' - ', OrdNo) as [Pad& Stand]
              Group By ID;
              LOAD ID,
                  SubField(Pad, ',', IterNo()) as Pad,
                  SubField(Stand, ',', IterNo()) as Stand,
                  IterNo() as OrdNo
              FROM [http://community.qlik.com/thread/130987]
              (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 5))))
              While IterNo() <= If(SubStringCount(Pad,',')>SubStringCount(Stand,','), SubStringCount(Pad,','), SubStringCount(Stand,','))+1;
              

               

               

              regards

               

              Marco

                • Re: Re: Re: try to nest a subfield loop
                  Marco Wedel

                  Hi,

                   

                  maybe like this?:

                   

                  QlikCommunity_Thread_130987_Pic2.JPG.jpg

                   

                  LOAD ID,
                       Concat('Pad. '&Pad&' Stand. '&Stand, ' - ', RecNo()) as [Pad& Stand]
                  Group By ID;
                  LOAD ID,
                       Pad,
                       Concat(Stand, ' ', RecNo()) as Stand
                  Group By ID, Pad;
                  LOAD ID, 
                       SubField(Pad, ',', IterNo()) as Pad, 
                       SubField(Stand, ',', IterNo()) as Stand
                  FROM [http://community.qlik.com/thread/130987]
                  (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 5))))
                  While IterNo() <= If(SubStringCount(Pad,',')>SubStringCount(Stand,','), SubStringCount(Pad,','), SubStringCount(Stand,','))+1;
                  

                   

                  hope this helps

                   

                  regards

                   

                  Marco

                    • Re: try to nest a subfield loop

                      Hi Marco Great job

                       

                      I have only add again the purgechar for get all the data (some dirty spaces in excel) and works perfectly..i don't know how but works

                       

                      thanks a lot you save me

                       

                      tabData:

                      LOAD ID,

                           Concat('Pad. '&Pad&' Stand. '&Stand, ' - ', RecNo()) as [Pad& Stand]

                      Group By ID;

                      LOAD ID,

                           Pad,

                           Concat(Stand, ' ', RecNo()) as Stand

                      Group By ID, Pad;

                      LOAD ID,

                           SubField(purgechar(Pad, chr(10)), ',', IterNo()) as Pad,

                           SubField(purgechar(Stand, chr(10)), ',', IterNo()) as Stand

                          

                          

                      FROM [C:\Users\silvas\Desktop\EMP -elabora dati\EICMA\catalogo_completo_25.xlsx]

                      (ooxml, embedded labels, table is Recuperati_Foglio1)

                      While IterNo() <= If(SubStringCount(Pad,',')>SubStringCount(Stand,','), SubStringCount(Pad,','), SubStringCount(Stand,','))+1;

                • Re: try to nest a subfield loop
                  Jonathan Dienst

                  Hi

                   

                  No need for a loop. Subfield(2 parameters) takes care of that for you:

                   

                  T_Data:

                  LOAD

                      'Pad ' & Pad & ' Stand ' & Stand As EachCombo

                  ;

                  LOAD ID,

                      subfield(purgechar(Pad, chr(10)),',') As Pad,

                      subfield(purgechar(Stand, chr(10)),',') As Stand,

                  FROM ...

                   

                  Results:

                  LOAD Concat(EachCombo, ' - ') As [Pad&Stand]

                  Resident T_Data;

                   

                  This will create records for all combinations of Pad and Stand and will list the combinations in the Pad&Stand field.

                   

                  HTH

                  Jonathan