2 Replies Latest reply: Sep 24, 2018 12:39 PM by omar bensalem RSS

    Manipulate a table

    Tiziano Gelso

      Hi,

      I would like to make a table like this:

      MonthKeyObject Type AObject Type BPlayer Type APlayer Type B

      10

      00112312
      100021223100
      11003144229
      1200416450

       

      into one like this:

       

      MonthKeyType AnalysisType AType B
      10001Object12
      10001Player312
      10002Object1243
      10002Player100
      11003Object1442

       

      I tried to make this:


      Crosstable (TypeAnalysis, Type, 2)

      [TEST_TABLE]:

      LOAD

            Month,

           Key,

           Object Type A,

           Object Type B,

           Player Type A,

           Player Type B

      FROM [lib://QVD/TEST_TABLE.QVD]

      (qvd);

       

      [TEST_TABLE_DERIVATE]:

      LOAD *,

          if (SubStringCount((UPPER(TypeAnalysis)),'OBJECT')<>0,'Object' , 'Player', as AnalysisField,

            if (SubStringCount((UPPER(Dimensione)),'A')=0,'Type A','Type B') as TypeField

      resident TEST_TABLE;


      But , in this way, I make this:


      MonthKeyType AnalysisTypeAnalisys FieldTypeField
      10001Object Type A1ObjectType A
      10

      001

      Object Type B2ObjectType B
      10001Player Type A3PlayerType A
      10001Player Type B12PlayerType B


       

      Can you help me? Thanks

        • Re: Manipulate a table
          youssef belloum

          Hi,

           

          I suppose your crosstable is correct, try this:

           

          Crosstable (TypeAnalysis, Type, 2)

          [TEST_TABLE]:

          LOAD

                Month,

               Key,

               Object Type A,

               Object Type B,

               Player Type A,

               Player Type B

          FROM [lib://QVD/TEST_TABLE.QVD]

          (qvd);


          table2:

          load TextBetween(Type,'',' ') as Type1, * Resident TEST_TABLE;

           

          drop table TEST_TABLE;


          ==> check if the field Type1 is ok for you

          • Re: Manipulate a table
            omar bensalem

            Try :

             

            t:

            load * inline [

            Month, Key, Object Type A, Object Type B, Player Type A, Player Type B

            10, 001, 1, 2, 3, 12

            10 ,002, 12, 23, 10, 0

            11 ,003, 14 ,42, 2, 9

            12 ,004, 16 ,4, 5 ,0

            ];

             

             

            final:

            CrossTable(TypeAnalysis, TypeA,2)

            load Month, Key, "Object Type A" as Object , "Player Type A" as Player Resident t;

             

             

            f:

            CrossTable(TypeAnalysis, TypeB,2)

            load Month, Key, "Object Type B" as Object , "Player Type B" as Player Resident t;

             

            LEFT JOIN(final)

            load * Resident f;

             

             

            drop Table f;

            drop Table t;

             

            result:

            Capture.PNG