How to create a new dimension by calculating two dimensions from different tables in the script?

[thiagobarbosa90]

Hello guys,

I have two date / time dimensions in separate tables. I need to do a simple calculation with these two fields. When I do this on an object it works normally. But I need this calculation in the script in order to create a new dimension of the result of that calculation.

How do I calculate dimensions from different tables?

interval( Plan_Cheg_DateTimeMIN - Plan_Dptr_DateTimeMAX ,'hh:mm')   as newdimension

Thanks in advance.

[vishsaggi · Accepted solution]

Not sure if this is the right way but may be give it a try:

LinkTable:

LOAD DISTINCT

     TrackingNumber,

     Plan_Cheg_DateTimeMin

Resident DL;

Concatenate

LOAD DISTINCT

     TrackingNumber,

     Plan_Dptr_DateTimeMax

Resident PU;

Concatenate

LOAD DISTINCT TrackingNumber

Resident Chave;

NoConcatenate

FinalLink:

LOAD *,

     Interval(Plan_Dptr_DateTimeMax - Plan_Cheg_DateTimeMin, 'hh:mm') AS NewDateTimeField

Resident LinkTable;

Drop Table LinkTable;

[thiagobarbosa90]

Thanks for reply me @Vishwarath Nagaraju,

I am useing what you sent me as a template then I made a couple of changes. I still don't have the result I want. But I believe we are in the right way. The dimension was created but not its result. Why did you use * after LOAD?

---------------------------------------------------------------------------------------------------------------

LinkTable:

LOAD DISTINCT

     TrackingNumber,

     Plan_Cheg_DateTimeMIN

Resident DL;

Concatenate

LOAD DISTINCT

     TrackingNumber,

     Plan_Dptr_DateTimeMAX

Resident PU;

Concatenate

LOAD DISTINCT TrackingNumber

Resident Chave;

NoConcatenate

TransitTime:

LOAD DISTINCT

TrackingNumber,

Interval(Plan_Cheg_DateTimeMIN - Plan_Dptr_DateTimeMAX,'hh:mm') as TT

Resident LinkTable;

Drop Table LinkTable;

---------------------------------------------------------------------------------------------------------------

[vishsaggi]

Load * is equivalent to SELECT * in SQL. It pulls all the fields from the table.

And does your plan_cheg_datetimemin and plan_cheg_datetimemax are in same format?

[thiagobarbosa90]

Vishwarath Nagaraju,

I got it!

I used pretty much the same structure that you showed me. But I've changed CONCATENATE to JOIN, then it finally worked.

I appreciated your help! Thanks a lot.

(fields are with the same format)