topic Re: using intervalmatch multiply qty and unitprice in QlikView

using intervalmatch multiply qty and unitprice

Anonymous — Wed, 25 Jul 2012 17:46:32 GMT

tab2

Quantity	Order Date	ItemID
10	30-Jun-00	1
20	30-Jun-99	1
30	04-Jul-00	1
10	03-Dec-99	2
30	07-Apr-00	2
20	07-Jul-00	2
40	09-Jan-00	2
10	10-Jun-00	3
20	11-Jul-99	3
30	12-Jul-99	3
30	04-Jul-12	1
30	04-Jul-11	1

tab1

ItemID	Price Changed Start Date	Price Changed End Date	Unit Price
1	01-Jul-00	04-Jul-00	25
1	01-Jul-99	30-Jun-00	10
1	05-Jul-00	10-Jul-00	26
1	11-Jul-00	05-Aug-00	28
1	06-Aug-00	05-Aug-12	28
2	04-Dec-99	07-Jan-99	30
2	06-Aug-00	06-Oct-00	33
2	07-Jul-00	05-Aug-00	35
2	08-Jan-99	06-Jul-00	28
3	09-Jun-00	09-Jul-00	15
3	13-Jul-99	08-Jun-00	20
3	12-Jan-99	12-Jul-99	21

i want to make qty*unitprice and try to implement interval_match.

please let me know how will i do this.

i am really new in this section. i have writen the following code.

tab1:

LOAD ItemID,

[Price Changed Start Date] as st_date,

[Price Changed End Date] as end_date,

[Unit Price] as unit_price

FROM

..\..\QLIKVIEW\interval_date.xls

(biff, embedded labels, table is actual$);

tab2:

LOAD Quantity as qty,

[Order Date] as ord_date,

ItemID

FROM

..\..\QLIKVIEW\interval_date.xls

(biff, embedded labels, table is sold$);

inner join

INTERVALMATCH (ord_date, ItemID)

LOAD

st_date

,end_date

,ItemID

,unit_price

RESIDENT [tab1];

Re: using intervalmatch multiply qty and unitprice

Wed, 25 Jul 2012 19:30:09 GMT

there is sometring strange with date format but i finally made it:)

and second thing check your intervals (one of them ends before start:)) so i use my own tables (similar to your's)

SET DateFormat='DD-MM-YYYY';

tab1:

LOAD * INLINE [

ItemID, st_date, end_date, unit_price

1, 01-07-1999, 30-06-2000, 10

1, 01-07-2000, 04-07-2000, 25

1, 05-07-2000, 10-08-2000, 26

1, 11-08-2000, 05-08-2012, 28

2, 08-01-1999, 06-07-2000, 28

2, 07-07-2000, 05-08-2000, 35

2, 06-08-2000, 06-10-2000, 33

3, 12-01-1999, 12-07-1999, 21

3, 13-07-1999, 08-01-2000, 20

3, 09-01-2000, 09-07-2000, 15

];

tab2:

LOAD * INLINE [

ItemID, ord_date, qty

1, 30-06-2000, 10

1, 30-06-1999, 20

1, 04-07-2000, 30

2, 03-12-1999, 10

2, 07-04-2000, 30

2, 09-01-2000, 40

3, 10-01-2000, 10

3, 11-07-1999, 20

3, 12-07-1999, 30

];

final:

IntervalMatch ( ord_date, ItemID ) load st_date, end_date, ItemID resident tab1;

left join load ItemID, ord_date, qty resident tab2;

left join load st_date, end_date, unit_price resident tab1;

drop table tab1, tab2;

left join load ItemID, ord_date, qty*unit_price resident final;

Re: using intervalmatch multiply qty and unitprice

Anonymous — Thu, 26 Jul 2012 17:46:35 GMT

thanks prai.

i have another query may be not required right now.

if stdat and enddate stored into a single column then how will i pick the

value.

itemid dt unitprice

1 01-07-1999 10 -- 1/7 to 29/6 value would be 10

1 30-06-2000 20

tab2 same as above.....

On Thu, Jul 26, 2012 at 1:00 AM, Pari Pari <