topic Re: link with date range in QlikView

link with date range

Fri, 19 Sep 2014 12:29:33 GMT

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?

Re: link with date range

anbu1984 — Fri, 19 Sep 2014 12:57:11 GMT

Can you show us what you have tried?

Re: link with date range

MK_QSL — Fri, 19 Sep 2014 13:01:43 GMT

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;

Re: link with date range

sundarakumar — Sat, 20 Sep 2014 13:17:38 GMT

Have u linked these table in the back end?

-Sundar

Re: link with date range

MarcoWedel — Sat, 20 Sep 2014 21:00:41 GMT

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

Re: link with date range

MarcoWedel — Sat, 20 Sep 2014 21:05:49 GMT

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

Re: link with date range

vikasmahajan — Sun, 21 Sep 2014 14:01:14 GMT

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

Vikas

Re: link with date range

Mon, 22 Sep 2014 09:57:05 GMT

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.