Feeling Brave??????

[samuel_brierley]

Hi all,

this has been bugging me for days, basically I have four tables and a very intensive formula that requires all 4.

calculating in a table takes ages so I thought lets move it to the script. good idea right?? one problem not the foggiest what to do.

attached is a sample, if someone can move the calculation to the script in some way id be very grateful.

in an ideal world the script would result in the table as it appears in the sample so I can then store it as a qvd and bin off the rest of the tables.

thanks all

[sunny_talwar · Accepted solution]

Since I did not have the original data source, I had to do a partial reload solution for the script, but you should be able to implement the same with a regular reload(remove Add next to the LOAD from each of the tables created)

Script:

LOAD [Geographic Address],

    [CO Address],

    [LCC Address],

    [Transponder Address],

    [Device Address],

    Date,

    Time(Time,'hh:mm') as Time,

    hour(Time) as [Hour of Day],

    [Number of Lanes],

    [Flow(Category 1)],

    [Flow(Category 2)],

    [Flow(Category 3)],

    [Flow(Category 4)],

    [Speed(Lane 1)],

    [Speed(Lane 2)],

    [Speed(Lane 3)],

    [Speed(Lane 4)],

    [Speed(Lane 5)],

    [Speed(Lane 6)],

    [Speed(Lane 7)],

    [Average Speed],

    [Flow(Lane 1)],

    [Flow(Lane 2)],

    [Flow(Lane 3)],

    [Flow(Lane 4)],

    [Flow(Lane 5)],

    [Flow(Lane 6)],

    [Flow(Lane 7)],

    [Average Flow],

    [Occupancy(Lane 1)],

    [Occupancy(Lane 2)],

    [Occupancy(Lane 3)],

    [Occupancy(Lane 4)],

    [Occupancy(Lane 5)],

    [Occupancy(Lane 6)],

    [Occupancy(Lane 7)],

    [Average Occupancy],

    [Headway(Lane 1)],

    [Headway(Lane 2)],

    [Headway(Lane 3)],

    [Headway(Lane 4)],

    [Headway(Lane 5)],

    [Headway(Lane 6)],

    [Headway(Lane 7)],

    [Average Headway],

    RCC

FROM

(qvd);

[Nox]:

LOAD [Vehicle type] as [Vehicle Type],

    Size,

    Fuel,

    [Euro standard],

    [Average Speed (km/h)] as [Average speed (km/h)],

    [NOx EF (g/km)]

FROM

(ooxml, embedded labels, table is HGV);

concatenate(Nox)

LOAD [Vehicle type] as [Vehicle Type],

    [Fuel / Size],

    Fuel,

    Size,

    [Euro standard],

    [Average speed (km/h)],

    [NOx EF (g/km)]

FROM

(ooxml, embedded labels, table is LGV);

concatenate(Nox)

LOAD [Vehicle type] as [Vehicle Type],

    [Fuel / Size],

    Fuel,

    Size,

    [Euro standard],

    [Average speed (km/h)],

    [NOx EF (g/km)]

FROM

(ooxml, embedded labels, table is Car);

[On road Composition]:

LOAD [Vehicle Type],

    [Hour of Day],

    [Vehilce Type Ratio]

FROM

(ooxml, embedded labels, table is [Hour of Day]);

[Euro Class breakdown]:

LOAD

if([Vehicle Type] = 'Diesel LGV' ,'LGV',[Vehicle Type]) as [Vehicle Type],

    Year,

    [Pre-Euro 1],

    [Euro 1],

    [Euro 2],

    [Euro 3],

    [Euro 4],

    [Euro 5],

    [Euro 6]

FROM

(ooxml, embedded labels, table is [Vehicle Type]);

MyTable:

Add LOAD [Flow(Lane 1)],

  [Hour of Day],

  [Speed(Lane 1)],

  Time,

  [Geographic Address],

  Time & '|' & [Geographic Address] as Group

Resident [North East RCC MIDAS];

Join (MyTable)

Add LOAD [Hour of Day],

  [Vehicle Type],

  [Vehilce Type Ratio]

Resident [On road Composition];

Join (MyTable)

Add LOAD [Vehicle Type],

  [Average speed (km/h)],

    [NOx EF (g/km)],

    [Euro standard]

Resident Nox;

Join (MyTable)

Add LOAD [Vehicle Type],

  [Euro 1]

Resident [Euro Class breakdown];

MyManipulation1:

Add Mapping LOAD Group,

  Only([Flow(Lane 1)]) as [Flow(Lane 1)]

Resident MyTable

Group By Group;

MyManipulation2:

Add Mapping LOAD Group,

  Avg([Vehilce Type Ratio]) as [Avg Vehilce Type Ratio]

Resident MyTable

Where [Vehicle Type] = 'Rigid'

Group By Group;

MyManipulation3:

Add Mapping LOAD Group,

  Avg([Euro 1]) as [Avg Euro1]

Resident MyTable

Where [Vehicle Type] = 'Rigid'

Group By Group;

MyManipulation4:

Add Mapping LOAD Group,

  Avg([NOx EF (g/km)]) as [Avg NOx EF (g/km)]

Resident MyTable

Where [Vehicle Type] = 'Rigid' and [Average speed (km/h)] = [Speed(Lane 1)] and [Euro standard] = 'Euro I'

Group By Group;

QUALIFY *;

FT:

Add LOAD *,

  ApplyMap('MyManipulation1', Group) * ApplyMap('MyManipulation2', Group) * ApplyMap('MyManipulation3', Group) * ApplyMap('MyManipulation4', Group) as Result

Resident MyTable;

UNQUALIFY *;

DROP Table MyTable;

Output Comparison:

One on the left right is the one from the script and the one on the right left is what you had.

Just so you know it took some time to reload because of the join we had in there. I am attaching the application as well as the logfile for you to review.

HTH

Best,

Sunny

[sunny_talwar]

To optimize the load time, you may be able to add the where condition during the joins like for instance Vehicle type = 'Rigid' is something which may reduce the number of observations for join greatly which might help with optimization

Best,

Sunny

[sunny_talwar]

Just couldn't stop myself from optimizing it . Pasting the new code only:

MyTable:

Add LOAD [Flow(Lane 1)],

  [Hour of Day],

  [Speed(Lane 1)],

  Time,

  [Geographic Address],

  Time & '|' & [Geographic Address] as Group

Resident [North East RCC MIDAS];

Join (MyTable)

Add LOAD [Hour of Day],

  [Vehicle Type],

  [Vehilce Type Ratio]

Resident [On road Composition]

Where [Vehicle Type] = 'Rigid';

Join (MyTable)

Add LOAD [Vehicle Type],

  [Average speed (km/h)],

    [NOx EF (g/km)],

    [Euro standard]

Resident Nox

Where [Vehicle Type] = 'Rigid' and [Euro standard] = 'Euro I';

Join (MyTable)

Add LOAD [Vehicle Type],

  [Euro 1]

Resident [Euro Class breakdown]

Where [Vehicle Type] = 'Rigid';

MyManipulation1:

Add Mapping LOAD Group,

  Only([Flow(Lane 1)]) as [Flow(Lane 1)]

Resident MyTable

Group By Group;

MyManipulation2:

Add Mapping LOAD Group,

  Avg([Vehilce Type Ratio]) as [Avg Vehilce Type Ratio]

Resident MyTable

Group By Group;

MyManipulation3:

Add Mapping LOAD Group,

  Avg([Euro 1]) as [Avg Euro1]

Resident MyTable

Group By Group;

MyManipulation4:

Add Mapping LOAD Group,

  Avg([NOx EF (g/km)]) as [Avg NOx EF (g/km)]

Resident MyTable

Where [Average speed (km/h)] = [Speed(Lane 1)]

Group By Group;

QUALIFY *;

FT:

Add LOAD *,

  ApplyMap('MyManipulation1', Group) * ApplyMap('MyManipulation2', Group) * ApplyMap('MyManipulation3', Group) * ApplyMap('MyManipulation4', Group) as Result

Resident MyTable;

UNQUALIFY *;

DROP Table MyTable;

Attaching the new script as well as the new logfile.

HTH

Best,

Sunny

[samuel_brierley]

Thank you very much that's fantastic your a great help!!!!!

[sunny_talwar]

Not a problem. I am glad I was able to help

Sunny