topic Re: Intervall match question in QlikView

Intervall match question

Thu, 11 Jun 2015 10:37:11 GMT

I have a fact table with dates, customers and other expressions

Now i want to connect a table with fare prices

This is a table with faire prices for every customer with a start and end date.

for example :

FarePrice:
LOAD
      %PK_Abonnee as %PK_FareAbonnee,
       Date(StartDate,'DD-MM-YYYY') as StartDate,
       IF(isnull(EndDate)=-1,'31-12-2015',EndDate) as EndDate,
          [Fare price]
FROM
.......

Facttable:

Load

%PK_FareAbonnee,

%PK_Date

#...

#.....

How can i connect this tables so i for every date and customer i can get the good fareprice

Who can help me?

Regards,

Dirk

Re: Intervall match question

alexandros17 — Thu, 11 Jun 2015 10:41:39 GMT

Just write the following instruction after the load of the 2 tables.

IntervalMatch (%PK_Date) LOAD StartDate, EndDate Resident FarePrice;

let me know

Re: Intervall match question

ganeshreddy — Thu, 11 Jun 2015 10:47:45 GMT

Hi Groen,

Please try this,

FarePrice:
LOAD
%PK_Abonnee as %PK_FareAbonnee,
Date(StartDate,'DD-MM-YYYY') as StartDate,
IF(isnull(EndDate)=-1,'31-12-2015',EndDate) as EndDate,
[Fare price]
FROM
.......

Facttable:
Load
%PK_FareAbonnee,
%PK_Date
#...
#.....
Inner join(FarePrice)
Intervalmatch(%PK_Date)
load distinct StartDate,EndDate, %PK_Date resident FarePrice;

Re: Intervall match question

Thu, 11 Jun 2015 10:48:10 GMT

But that works for everydate but not for every date and customer.

Do you understand what i mean.

Re: Intervall match question

alexandros17 — Thu, 11 Jun 2015 10:53:55 GMT

Could you share your qvw and describe with more details your objective?

Re: Intervall match question

Thu, 11 Jun 2015 11:08:43 GMT

Thanks Ganesh, it works,

Regards,

Dirk