Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
thiagobarbosa90
Contributor II
Contributor II

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

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?


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


Capturar.JPG


Thanks in advance.

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

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;

View solution in original post

4 Replies
vishsaggi
Champion III
Champion III

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
Contributor II
Contributor II
Author

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
Champion III
Champion III

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
Contributor II
Contributor II
Author

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)