Creation of new dimension

[#1]

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;

[Anonymous]

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?

[#3]

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

[settu_periasamy · Accepted solution]

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;