7 Replies Latest reply: Jan 24, 2012 7:27 PM by javierortiz RSS

    How can I get one simple month field if I have 2 month fields?

      Hi Guys, How could I get one simple month field if I have 2 month fields from diferent tables? one month field comes from a crossed table.
      I Attached the real Example but the example below is kind of close.
      Than kou I appreciate your time Guys!!
      Table1:
      Load
      Date
      month(Date) as Month
      from xlxs.
      Table2:
      Crosstable(Month, Numbers)
      Load
      Jan2012,
      Feb2012,
      Mar2012,
      Abr2012,
      May2012,
      Jun2012,
      Jul2012,
      Ago2012,
      Sep2012,
      Oct2012,
      Nov2012,
      Dic2012
      From xlxs
        • How can I get one simple month field if I have 2 month fields?
          Stefan Wühl

          I haven't really looked into your attached sample app, I assume you just want to link the two field by name (Month). To get the two fields linked appropriately, the values should have the same format, this is what iI think is missing in your case:

           

          Month(Date) is returning a dual, a text representation like Jan, Feb, Mar (depending on your sys settings), and the month number as numerical representation.

           

          Your Month from Crosstable load will probably just read in as text, but you could convert it to the same format as in your first table:

           

           

          Crosstable (Month, Numbers)

          LOAD month(Date#(Month,'MMMYYYY')) as Month, OtherField1, OtherField2, ...

          from Excel.xls;

           

           


          Hope this helps,

          Stefan

            • How can I get one simple month field if I have 2 month fields?

              Hi Stefan, I think you are right, it should be something about format, but I can´t make it working, would you take a look at the App?

               

              I´m sorry if I´m taking your time.

               

               

              Tnaks!!

                • Re: How can I get one simple month field if I have 2 month fields?
                  Miguel Angel Baeyens de Arce

                  Hi Javier,

                   

                  Since months are dual values (numeric values with string representation) try using 1, 2, 3 and so on instead "Ene", "Feb", "Mar", and so. You might need to do two loads so the format is actually a month. I have been looking at your script and it should look like this instead:

                   

                  Ptto:
                  CrossTable(Mes_Ptto, Datos)
                  LOAD [Número de Parte]             as Material, 
                       ENE                           as 1, 
                       FEB                           as 2, 
                       MAR                           as 3, 
                       ABR                           as 4, 
                       MAY                           as 5, 
                       JUN                           as 6, 
                       JUL                           as 7, 
                       AGO                           as 8, 
                       SEP                           as 9, 
                       OCT                           as 10, 
                       NOV                           as 11, 
                       DIC                           as 12
                  FROM
                  [Ppto Entradas.xlsx]
                  (ooxml, embedded labels, table is [QV SR]);
                  

                   

                  I cannot reload and test because there is no excel file attached.

                   

                  Hope that helps.

                   

                  Miguel

                   

                  EDIT: Definitely you need to do a two step load, because the field name is literal, not numeric. See attached application for further reference.

              • Re: How can I get one simple month field if I have 2 month fields?

                I finally got the sollution, we were really close, the solution was, using the snetence in script :

                 

                 

                MONTH(MAKEDATE(0, NUM#(Mes_Numerico))) as Mes,

                 

                 

                 

                Thank you Miguel, Thank you Stefan, You Guys saved my day!!

                 

                 

                Have a grat Day!!