3 Replies Latest reply: Nov 27, 2015 12:42 PM by Settu Periyasamy RSS

    Creation of new dimension

    Dingyong Huang

      Hi all

       

      Based on the script below, I am trying to create 1 new dimension for DEP_CARRIER/MAX_DEP_CARRIER + DEP_FLIGHTS/MAX_DEP_FLIGHTS and group it by ORIGIN_AIRPORT_ID.


      There is no need to group the calculated result by date.


      Would appreciate any help. Thanks!!!

       

      ++++++++++++++++++++++++++++++++

      OTP:

          FL_DATE,

          UNIQUE_CARRIER,

          ORIGIN_AIRPORT_ID,

      FROM

      [$(vpath)Jan2015.csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

       

      CARRIER:

      LOAD

      ORIGIN_AIRPORT_ID,

      Count (DISTINCT UNIQUE_CARRIER) as DEP_CARRIER

      Resident OTP

      Group by ORIGIN_AIRPORT_ID;

       

      MAX_CARRIER:

      LOAD

      Max (DEP_CARRIER) as MAX_DEP_CARRIER

      Resident CARRIER;

       

      FLIGHTS:

      LOAD

      ORIGIN_AIRPORT_ID,

      Count (FL_DATE) as DEP_FLIGHTS

      Group by ORIGIN_AIRPORT_ID;

       

      MAX_FLIGHTS:

      LOAD

      Max (DEP_FLIGHTS) as MAX_DEP_FLIGHTS

      Resident FLIGHTS;

        • Re: Creation of new dimension
          balraj ahlawat

          If I am not wrong, It is not your dimension, it would be your measure which may be a integer or decimal value like 345,56.78..

           

          How it can be a dimension?

            • Re: Creation of new dimension
              Dingyong Huang

              Would it be clearer if I say I am looking to create a new dimension based on the stated expression?

                • Re: Creation of new dimension
                  Settu Periyasamy

                  Hi,

                  check this Script..

                  OTP:
                      FL_DATE,
                      UNIQUE_CARRIER,
                      ORIGIN_AIRPORT_ID,
                  FROM
                  [$(vpath)Jan2015.csv]
                  (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
                  
                  
                  NoConcatenate
                  CARRIER:
                  LOAD
                  ORIGIN_AIRPORT_ID,
                  Count (DISTINCT UNIQUE_CARRIER) as DEP_CARRIER
                  Resident OTP
                  Group by ORIGIN_AIRPORT_ID;
                  
                  
                  Left Join(CARRIER)
                  
                  
                  MAX_CARRIER:
                  LOAD
                  Max (DEP_CARRIER) as MAX_DEP_CARRIER
                  Resident CARRIER;
                  
                  
                  NoConcatenate
                  FLIGHTS:
                  LOAD
                  ORIGIN_AIRPORT_ID,
                  Count (FL_DATE) as DEP_FLIGHTS Resident OTP
                  Group by ORIGIN_AIRPORT_ID ;
                  
                  Left Join(FLIGHTS) 
                  
                  
                  MAX_FLIGHTS:
                  LOAD
                  Max (DEP_FLIGHTS) as MAX_DEP_FLIGHTS
                  Resident FLIGHTS;
                  
                  
                  NoConcatenate
                  CARRIER_NEW:
                  LOAD * Resident CARRIER;
                  Left Join(CARRIER_NEW)
                  LOAD * Resident FLIGHTS;
                  
                  
                  DROP Tables OTP,CARRIER,FLIGHTS;
                  
                  
                  NoConcatenate
                  FINAL:
                  LOAD *,
                    (DEP_CARRIER/MAX_DEP_CARRIER)+(DEP_FLIGHTS/MAX_DEP_FLIGHTS) as New_Dim
                  Resident CARRIER_NEW;
                  DROP Table CARRIER_NEW;