Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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?
Would it be clearer if I say I am looking to create a new dimension based on the stated expression?
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;