link with date range

[#1]

Hi,

I have a question with linking 3 tables.

Debtor(1.200 records)

DebtorCode	Debtorname	PriceCode
1	John	2
2	Pierre	3
3	Smith	2

Invoice(60.000 records)

InvoiceCode	DebtorCode	InvoiceDate
1	2	01-01-2013
2	3	02-03-2013

Price(400 records)

PriceCode	StartDate	EndDate	Price	Price per 100	Price per 1000
1	01-01-2013	03-04-2013	10	90	800
2	01-02-2013	05-05-2013	12	95	850
3	01-05-2013	10-10-2014	16	120	900
2	01-01-2013	31-01-2013	10	100	1000

What I need to know is the following

InvoiceCode	Debtorname	InvoiceDate	Price	Price per 100	Price per 1000
1	Pierre	01-01-2013	16	120	900
2	Smith	02-03-2013	12	95	850

Normally i would just do this with an If statement in the expression.

But when i tried it this time it used about 12GB of RAM. not very usefull....

Do you know some way to make it possible to create this overview?

[anbu1984]

Can you show us what you have tried?

[MK_QSL · Accepted solution]

Debtor:

Load * Inline

[

  DebtorCode, Debtorname, PriceCode

  1, John, 2

  2, Pierre, 3

  3, Smith, 2

];

Invoice:

Load

  InvoiceCode,

  DebtorCode,

  Date(Date#(InvoiceDate,'DD-MM-YYYY')) as InvoiceDate

Inline

[

  InvoiceCode, DebtorCode, InvoiceDate

  1, 2, 01-01-2013

  2, 3, 02-03-2013

];

Left Join (Invoice) Load * Resident Debtor;

Drop Table Debtor;

Price:

Load

  PriceCode,

  Date(Date#(StartDate,'DD-MM-YYYY')) as StartDate,

  Date(Date#(EndDate,'DD-MM-YYYY')) as EndDate,

  Price,

  [Price per 100],

  [Price per 1000]

Inline

[

  PriceCode, StartDate, EndDate, Price, Price per 100, Price per 1000

  1, 01-01-2013, 03-04-2013, 10, 90, 800

  2, 01-02-2013, 05-05-2013, 12, 95, 850

  3, 01-05-2013, 10-10-2014, 16, 120, 900

  4, 01-01-2013, 31-01-2013, 10, 100, 1000

];

IntervalMatch(InvoiceDate, PriceCode)

Load StartDate, EndDate, PriceCode Resident Price;

Inner Join (Invoice) Load * Resident Price;

Drop Table Price;

[sundarakumar]

Have u linked these table in the back end?

-Sundar

[MarcoWedel]

correction sample data

PriceCode	StartDate	EndDate	Price	Price per 100	Price per 1000
1	01-01-2013	03-04-2013	10	90	800
2	01-02-2013	05-05-2013	12	95	850
3	01-05-2012	10-10-2014	16	120	900
2	01-01-2013	31-01-2013	10	100	1000

[MarcoWedel]

Hi,

one possible solution could be to include the relevant price data in the invoice table:

Debtor:
LOAD *
FROM [http://community.qlik.com/thread/134144] (html, codepage is 1252, embedded labels, table is @1);

Invoice:
LOAD *
FROM [http://community.qlik.com/thread/134144] (html, codepage is 1252, embedded labels, table is @2);

Price:
LOAD *
FROM [http://community.qlik.com/thread/134144] (html, codepage is 1252, embedded labels, table is @5);

Left Join (Invoice)
LOAD Distinct
  DebtorCode,
  PriceCode
Resident Debtor;

Left Join (Invoice)
IntervalMatch (InvoiceDate, PriceCode)
LOAD StartDate,
    EndDate,
    PriceCode
Resident Price;

Left Join (Invoice)
LOAD StartDate,
    EndDate,
    PriceCode,
    Price as price,
    [Price per 100] as [price per 100],
    [Price per 1000] as [price per 1000]
Resident Price;

DROP Fields PriceCode, StartDate, EndDate From Invoice;

hope this helps

regards

Marco

[vikasmahajan]

you can interval match for achieving the same find attache mt of  demo will help you to achieve the same.

Vikas

[#8]

Ok so, the intervalmatch solution is the one I needed, it works, in theory that is. But after carefull analysis of the original datasource a problem occurred. Apparantly in the Price table there are overlapping ranges, so for 1 invoice i have 280 possible prices, we will have to solve that first, in the datasource. I will mark the first 2 correct answers, thank you all for the help. I really appreciate it.