3 Replies Latest reply: Jul 8, 2013 10:24 AM by Adriano Fornoni RSS

    Crosstable, Subfield and IntervalMatch

      Hi all,

      i've a table like this

       

      ID DesRG1RG2
      ITEM1Item used 66% timeGr11-01-19000101;2-01-20130615
      ITEM2Item used 15% timeGr11-01-19000101
      ITEM3Item never usedGr1

       

      I would like to obtain a final table like this:

      IDDesRG1MCTreeFromTo
      ITEM1Item used 66% timeGr11011900010120130615
      ITEM1Item used 66% timeGr120120130615
      ITEM2Item used 15% timeGr110119000101

       

      I don't be so good qlikview developer.

      I suppose that I've to use before a CrossTable and then a Subfield but i'm not able.

      Once I get the table represented, I can use it for an IntervalMatch function.

      In this table I've the minimum (From) and maximum (To) value.

       

      Anyone could help me?

      Please I'm in difficulty.

      Bye and thanks

      Adriano

        • Re: Crosstable, Subfield and IntervalMatch
          Gysbert Wassenaar

          Input:

          LOAD ID, Des, RG1, date#(right(subfield(RG2, ';'),8),'YYYYMMDD') as From INLINE [

              ID, Des, RG1, RG2

              ITEM1, Item used 66% time, Gr1, 1-01-19000101;2-01-20130615

              ITEM2, Item used 15% time, Gr1, 1-01-19000101

              ITEM3, Item never used, Gr1

          ];

           

          Result:

          NoConcatenate load

          ID, Des, RG1, From,

          if(previous(ID)=ID, previous(From)) as To

          Resident Input

          where len(trim(From))>0

          order by ID, From desc ;

           

          drop table Input;

           

          See attached example.

            • Re: Crosstable, Subfield and IntervalMatch

              Thanks, the answer is correct.

              Now I have to get the two columns MC and Tree but I hope to be able.

               

              Thank you very much

              I did't think you could do it in so little time.

               

              Bye

              Adriano

                • Re: Crosstable, Subfield and IntervalMatch

                  Ok, I obtained the desired result.

                  Thank you very much, I write the example with all the field managed.

                   

                  Input:

                  LOAD ID, Des, RG1, subfield(RG2,';') as FIELD1

                       INLINE [

                      ID, Des, RG1, RG2

                      ITEM1, Item used 66% time, Gr1, 1-01-19000101;2-01-20130615

                      ITEM2, Item used 15% time, Gr1, 1-01-19000101

                      ITEM3, Item never used, Gr1

                      ITEM4, ADRIANO, GR_AF, 5-01-19000101;2-01-20130701;3-03-20140115

                      ITEM5, ADRY, GR_AA, 1-02-19000101;2-02-20130701;3-02-20140115;4-02-20140320

                      ITEM6, ADRY, GR_AA, 4-01-19000101;3-02-20130701;2-03-20140115;1-04-20140515

                      ITEM7, ADRY_X, GR_AX, 1-01-19000101;3-02-20130701;2-03-20140115;5-04-20140620

                  ];

                   

                   

                  Result:

                  NoConcatenate load

                  ID, Des, RG1,

                  FIELD1,

                  if(previous(ID)=ID, previous(FIELD1)) as FIELD2

                  Resident Input

                  where len(trim(FIELD1))>0

                  order by ID, FIELD1 desc ;

                   

                  drop table Input;

                   

                   

                  ResultAdriano:

                  NoConcatenate

                  LOAD ID, Des, RG1

                  , left(FIELD1,1) as MC

                  , mid(FIELD1,3,2) as Tree

                  , date#(right(FIELD1,8),'YYYYMMDD') as From

                  , if(len(Right(FIELD2,8))>0,date(date#(Right(FIELD2,8),'YYYYMMDD')-1,'YYYYMMDD'),'22000101') as To

                  Resident Result

                  ;

                   

                  drop table Result;

                   

                  Bye

                  Adriano