Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Intervall match question

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

Tags (2)
1 Solution

Accepted Solutions
ganeshreddy
Contributor III

Re: Intervall match question

Hi Groen,

Please try this,

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

  1. Facttable: 
  2. Load 
  3.      %PK_FareAbonnee, 
  4.      %PK_Date 
  5.      #... 
  6.      #..... 


  7. Inner join(FarePrice)
  8. Intervalmatch(%PK_Date)
  9. load distinct StartDate,EndDate, %PK_Date resident FarePrice;
5 Replies

Re: Intervall match question

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

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

let me know

ganeshreddy
Contributor III

Re: Intervall match question

Hi Groen,

Please try this,

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

  1. Facttable: 
  2. Load 
  3.      %PK_FareAbonnee, 
  4.      %PK_Date 
  5.      #... 
  6.      #..... 


  7. Inner join(FarePrice)
  8. Intervalmatch(%PK_Date)
  9. load distinct StartDate,EndDate, %PK_Date resident FarePrice;
Not applicable

Re: Intervall match question

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

Do you understand what i mean.

Re: Intervall match question

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

Not applicable

Re: Intervall match question

Thanks Ganesh, it works,

Regards,

Dirk

Community Browser