Joining versioned tables

[Anonymous]

Hello!

Faced a problem developing a data model in Qlik. Need to merge several dimensions but all off them have their own duration. Using the best practices IntervalMatch and Slowly Changing Dimensions didn't not help cause we get only overlapping intervals.

With a partitioning technique we still get wrong results. The problem is OUTER JOIN while joining doesn't take time when the object is valid. My script now is:

consumer:
LOAD * inline [
    DwIDConsumer,FDConsumer,TDConsumer, IntIDConsumer
  25893, 2000-01-01 00:00:00.000, 2099-01-01 00:00:00.000, 190040
]   ;

contract:
LOAD * inline [
DwIDContract, FDContract,TDContract,IntIDConsumer
13618, 1995-10-01 00:00:00.000, 2012-12-31 23:59:59.000, 190040
16415, 2000-01-01 00:00:00.000, 2099-01-01 00:00:00.000, 190040
];

tempSubIntervals:
noconcatenate load distinct DwIDConsumer,IntIDConsumer,FDConsumer, TDConsumer resident consumer;
join
load distinct DwIDContract,IntIDConsumer,FDContract,TDContract
resident contract;


drop table consumer;
drop table contract;


tempSubIntervals2:
noconcatenate load *, Hash256(DwIDConsumer,DwIDContract) as subIntervalID resident tempSubIntervals;
drop table tempSubIntervals;


tempSubIntervals3:
noconcatenate load distinct subIntervalID,DwIDConsumer,DwIDContract,FDConsumer as SubFromDate resident tempSubIntervals2;
load distinct subIntervalID,DwIDConsumer,DwIDContract,TDConsumer as SubFromDate resident tempSubIntervals2;
load distinct subIntervalID,DwIDConsumer,DwIDContract,FDContract as SubFromDate resident tempSubIntervals2;
load distinct subIntervalID,DwIDConsumer,DwIDContract,TDContract as SubFromDate resident tempSubIntervals2;


drop table tempSubIntervals2;


subIntervals:
Load subIntervalID, DwIDConsumer,DwIDContract,date(SubFromDate, 'DD.MM.YYYY hh:mm:ss.fff')
as SubFromDate,
RecNo() as IntervalID,date(SubToDate - 1/86400, 'DD.MM.YYYY hh:mm:ss.fff')as SubToDate
Where not IsNull(SubToDate);
load subIntervalID, DwIDConsumer,DwIDContract,SubFromDate,
If(subIntervalID = Previous(subIntervalID), Previous(SubFromDate)) as SubToDate
Resident tempSubIntervals3
Order By subIntervalID, SubFromDate Desc;


drop table tempSubIntervals3;

The result I want would be:

DwIDConsumer	DwIDContract	SubFromDate	SubToDate	IntIDConsumer
-	13618	1995-10-01 00:00:00	1999-12-31 23:59:59	-
25893	13618	2000-01-01 00:00:00	2012-12-31 23:59:59	190040
25893	16415	2013-01-01 00:00:00	2099-01-01 00:00:00	190040

Probably first join of ovelapping intervals needed then concatenate with the rows that have no match in the second table... But I don't know how to do it. Could you please help?

I'm attaching the same app, as in qlikviewm the same in sense

[sunny_talwar]

How about this?

consumer:

LOAD * inline [

    DwIDConsumer,FDConsumer,TDConsumer, IntIDConsumer

  25893, 2000-01-01 00:00:00.000, 2099-01-01 00:00:00.000, 190040

]  ;

Join(consumer)

LOAD * inline [

DwIDContract, FDContract,TDContract,IntIDConsumer

13618, 1995-10-01 00:00:00.000, 2012-12-31 23:59:59.000, 190040

16415, 2000-01-01 00:00:00.000, 2099-01-01 00:00:00.000, 190040

];

FinalTable:

LOAD TimeStamp(If(FDConsumer > FDContract,

  If(AutoNumber(RowNo(), DwIDContract) = 1, FDContract, FDConsumer), FDContract)) as SubFromDate,

  TimeStamp(If(FDConsumer > FDContract,

  If(AutoNumber(RowNo(), DwIDContract) = 1, FDConsumer - 1/(60*24*60), TDConsumer), TDContract)) as SubToDate,

  If(Flag = 2 and AutoNumber(RowNo(), DwIDContract) = 2 or Flag = 1, DwIDConsumer) as DwIDConsumer,

  If(Flag = 2 and AutoNumber(RowNo(), DwIDContract) = 2 or Flag = 1, IntIDConsumer) as IntIDConsumer,

  DwIDContract

While IterNo() <= Flag;

LOAD If(FDConsumer > FDContract, 2, 1) as Flag,

  *

Resident consumer;

DROP Table consumer;

[Anonymous]

Thank you for your reply!

The first interval I was struggling the most is finally right!

But the two others aren't divided by 2013-01-01 00:00:00 date

[sunny_talwar]

Why is 16415 getting divided here? I am not sure I understand the reason for that. Can you explain a little?

[Anonymous]

My bad!

There is a mistake in data, it must be

consumer:

LOAD * inline [

    DwIDConsumer,FDConsumer,TDConsumer, IntIDConsumer

  25893, 2000-01-01 00:00:00.000, 2099-01-01 00:00:00.000, 190040

]  ;

contract:

LOAD * inline [

DwIDContract, FDContract,TDContract,IntIDConsumer

13618, 1995-10-01 00:00:00.000, 2012-12-31 23:59:59.000, 190040

16415, 2013-01-01 00:00:00.000, 2099-01-01 00:00:00.000, 190040

];

So there are 2 contracts bounded to 1 consumer. All these object has its own duration. And the whole period of its existence must be divided by the ends of each object ends:

1) from 1995-10-01 till 1999-12-31 contract 13618 without any consumer exists.

2) from 2000-01-01 till 2012-12-31 (the end of 13618 contract duration) exist both consumer 25893 and contract 13618 )

3) from 2013-01-01  till 2099-01-01 exist the same consumer 25893 and already another contract 16415

[sunny_talwar · Accepted solution]

May be this:

consumer:

LOAD * inline [

    DwIDConsumer,FDConsumer,TDConsumer, IntIDConsumer

  25893, 2000-01-01 00:00:00.000, 2099-01-01 00:00:00.000, 190040

]  ;

Join (consumer)

LOAD * inline [

DwIDContract, FDContract,TDContract,IntIDConsumer

13618, 1995-10-01 00:00:00.000, 2012-12-31 23:59:59.000, 190040

16415, 2013-01-01 00:00:00.000, 2099-01-01 00:00:00.000, 190040

];

TempTable:

NoConcatenate

LOAD FDConsumer,

  TDConsumer,

  FDContract,

  TDContract,

  DwIDConsumer,

  IntIDConsumer,

  AutoNumber(RowNo(), DwIDContract) as Flag3,

  If(Flag = 2 and AutoNumber(RowNo(), DwIDContract) = 2 or Flag = 1, 1, 0) as Flag2,

  DwIDContract

While IterNo() <= Flag;

LOAD If(FDConsumer > FDContract, 2, 1) as Flag,

  *

Resident consumer;

TempTable2:

LOAD DwIDConsumer,

  IntIDConsumer,

  DwIDContract,

  FDConsumer,

  TDConsumer,

  FDContract,

  TDContract,

  Flag2,

  TimeStamp(If(IntIDConsumer = Previous(IntIDConsumer),

  If(DwIDContract = Previous(DwIDContract), FDConsumer, FDContract), FDContract)) as SubFromDate

Resident TempTable

Order By IntIDConsumer, DwIDContract, Flag3;

FinalTable:

LOAD If(Flag2 = 1, DwIDConsumer) as DwIDConsumer,

  If(Flag2 = 1, IntIDConsumer) as IntIDConsumer,

  DwIDContract,

  SubFromDate,

  TimeStamp(If(IntIDConsumer = Previous(IntIDConsumer), RangeSum(Previous(SubFromDate), -MakeTime(0, 0, 1)), TDConsumer)) as SubToDate

Resident TempTable2

Order By IntIDConsumer, SubFromDate desc;

DROP Tables consumer, TempTable, TempTable2;

[Anonymous]

Sunny, many-many thanks!

It works fine now but only with cases when contract starts earlier. Intervals for almost all consumers, starting from 01-01-2000, are missed