topic Re: Joining versioned tables in QlikView

Joining versioned tables

Anonymous — Thu, 04 Aug 2016 10:56:20 GMT

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

Re: Joining versioned tables

sunny_talwar — Thu, 04 Aug 2016 17:17:22 GMT

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;

Re: Joining versioned tables

Anonymous — Fri, 05 Aug 2016 09:46:03 GMT

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

Re: Joining versioned tables

sunny_talwar — Fri, 05 Aug 2016 11:17:48 GMT

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

Re: Joining versioned tables

Anonymous — Fri, 05 Aug 2016 13:21:17 GMT

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

Re: Joining versioned tables

sunny_talwar — Fri, 05 Aug 2016 16:39:01 GMT

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;

Re: Joining versioned tables

Anonymous — Mon, 08 Aug 2016 08:50:35 GMT

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