Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Creation of new dimension

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;

1 Solution

Accepted Solutions
settu_periasamy
Not applicable

Re: Creation of new dimension

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;

3 Replies
balrajahlawat
Not applicable

Re: Creation of new dimension

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?

Not applicable

Re: Creation of new dimension

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

settu_periasamy
Not applicable

Re: Creation of new dimension

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;