5 Replies Latest reply: Sep 11, 2015 8:13 AM by Massimo Grossi RSS

    Table Format issue

    vir vir

      Hi,

       

      I have a Table having column like below:

       

      ID       Interested_City_X         Interested_City_Y          Interested_City_Z

      ----------------------------------------------------------------------------------------------------

      1                        1                                     0                                     0

      2                         0                                     1                                     0

      3                         0                                    0                                      1

       

       

      Instead of above i want like this:

       

      ID                   CIty

       

      1                      X

      2                       Y

      3                      Z

       

       

      How it possible ,kindly suggest possible way.

        • Re: Table Format issue
          Gowtham Kesavan

          Load * inline

          [

          ID ,CIty

          1 , X

          2 , Y

          3 ,  Z

          ];

          • Re: Table Format issue
            Teis thrane Wamsler

            Crosstable.PNG

             

            Hi Vir Vir

             

            It sure can.

            You can use the Crosstable funktion when you select your data to load in script wizard.

             

             

             

            /Teis

            • Re: Table Format issue
              Sunny Talwar

              You would need a CrossTable Load here:

               

              Table:

              LOAD * Inline [

              ID,       Interested_City_X,         Interested_City_Y,          Interested_City_Z

              1,                        1,                                     0,                                     0

              2,                        0,                                     1,                                     0

              3,                         0,                                    0,                                      1

              ];

               

              CrossTable:

              CrossTable(City, Data)

              LOAD *

              Resident Table;

               

              NewTable:

              LOAD SubField(City, '_', -1) as City,

                ID

              Resident CrossTable

              Where Data = 1;

               

              DROP Tables Table, CrossTable;


              Capture.PNG

              • Re: Table Format issue
                Gabriel Oluwaseye

                Hi,

                 

                CrossTable is the function to use in this case

                • Re: Table Format issue
                  Massimo Grossi

                  b:

                  load * inline [

                  ID    ,   Interested_City_X,         Interested_City_Y,          Interested_City_Z

                  1      ,                  1 ,                                    0,                                     0

                  2       ,                  0 ,                                    1,                                     0

                  3        ,                 0  ,                                  0  ,                                    1

                  ];

                   

                  t:

                  CrossTable(Interested_City_X, Data)

                  LOAD ID,

                       Interested_City_X,

                       Interested_City_Y,

                       Interested_City_Z

                  Resident b;

                  DROP Table b;

                   

                  z:

                  NoConcatenate LOAD

                  ID,

                  SubField(Interested_City_X, '_', 3) as City

                  Resident t where Data>0;

                  DROP Table t;