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

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

thiagobarbosa90 — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

vishsaggi — Wed, 01 Aug 2018 15:43:54 GMT

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;

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

thiagobarbosa90 — Wed, 01 Aug 2018 18:26:35 GMT

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;

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

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

vishsaggi — Wed, 01 Aug 2018 20:49:04 GMT

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?

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

thiagobarbosa90 — Thu, 02 Aug 2018 18:51:50 GMT

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)