topic Re: Price record between two dates in QlikView

Price record between two dates

Thu, 21 Apr 2016 13:52:09 GMT

Hi All,

What i am trying to do is get a price for a record where the SALES_TABLE "Date" is between two dates ( price.start , price.exp) and where the "Item_code" and Customer Name from SALES_TABLE is DAI000**

Re: Price record between two dates

sunny_talwar — Thu, 21 Apr 2016 15:15:29 GMT

Would you be able to provide the above screenshot data in an Excel file?

Re: Price record between two dates

Thu, 21 Apr 2016 16:37:23 GMT

Price_list

Item	Item_code	price	price.start	price.exp
ITEM_NAME_1	0102213	3,07	2010-04-12 00:00:00.000	2014-04-01
ITEM_NAME_2	0151003	7,47	2010-04-12 00:00:00.000	2014-12-01
ITEM_NAME_3	0105003	3,97	2014-04-12 00:00:00.000	2016-01-01
ITEM_NAME_4	0715206	3,47	2010-04-12 00:00:00.000	2014-05-31
ITEM_NAME_5	0602006	3,18	2013-04-12 00:00:00.000
ITEM_NAME_2	0151003	9,99	2014-12-02 00:00:00.000
ITEM_NAME_4	0715206	4,47	2014-06-01 00:00:00.000

Sales

ID	Item_code	Client_ID	Date	kg	price
18912	0102213	DAR002	2014-01-02	12,000	1,45
18913	1115102	IND015	2015-01-02	10,000	2,29
18914	0105003	DAI00012	2015-05-02	2,6	1,56
18915	0715206	DAI00007	2015-01-02	1309,000	1,1
18916	0602006	DAI00012	2016-03-02	0,885	4,08
18917	0151003	DAI00012	2015-01-02	1342,000	1,35

Re: Price record between two dates

Gysbert_Wassenaar — Thu, 21 Apr 2016 18:06:29 GMT

I don't understand the logic of your result table. But to match the Date from the Sales table with the interval in the Price_List table you need to use the Intervalmatch function. Try the script below and then add two table boxes for the two tables that are created in the script.

Note, the INLINE loads are just examples. You can use your excel file as a source instead.

Price_List:

LOAD

Item,

Item_code,

price,

Date(Date#(left(price.start,10),'YYYY-MM-DD'),'YYYY-MM-DD') as price.start,

Date(Alt(Date#(price.exp,'YYYY-MM-DD'),makedate(2099)),'YYYY-MM-DD') as price.exp

INLINE [

Item, Item_code, price, price.start, price.exp

ITEM_NAME_1, 0102213, "3,07", 2010-04-12 00:00:00.000, 2014-04-01

ITEM_NAME_2, 0151003, "7,47", 2010-04-12 00:00:00.000, 2014-12-01

ITEM_NAME_3, 0105003, "3,97", 2014-04-12 00:00:00.000, 2016-01-01

ITEM_NAME_4, 0715206, "3,47", 2010-04-12 00:00:00.000, 2014-05-31

ITEM_NAME_5, 0602006, "3,18", 2013-04-12 00:00:00.000,

ITEM_NAME_2, 0151003, "9,99", 2014-12-02 00:00:00.000,

ITEM_NAME_4, 0715206, "4,47", 2014-06-01 00:00:00.000,

];

Sales:

LOAD

ID,

Item_code,

Client_ID,

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

kg,

price as sales_price

INLINE [

ID, Item_code, Client_ID, Date, kg, price

18912, 0102213, DAR002, 2014-01-02, "12,000", "1,45"

18913, 1115102, IND015, 2015-01-02, "10,000", "2,29"

18914, 0105003, DAI00012, 2015-05-02, "2,6", "1,56"

18915, 0715206, DAI00007, 2015-01-02, "1309,000", "1,1"

18916, 0602006, DAI00012, 2016-03-02, "0,885", "4,08"

18917, 0151003, DAI00012, 2015-01-02, "1342,000", "1,35"

];

LEFT JOIN (Sales)

IntervalMatch(Date,Item_code)

LOAD

price.start,

price.exp,

Item_code

RESIDENT

Price_List

;

LEFT JOIN (Sales)

LOAD

price.start,

price.exp,

Item_code,

price as pricelist_price

RESIDENT

Price_List

;

DROP FIELDS price.start, price.exp FROM Sales;

Re: Price record between two dates

Pulkit_Thukral — Thu, 21 Apr 2016 18:17:05 GMT

Somewhat like :

only({<ClientID={'DAI000*'},Date={">=$(=date(floor(monthstart(addmonths(today()-14))),'DD-MM-YYYY'))"}*{'<$(=date(floor(monthstart(addmonths(today()-8))),'DD-MM-YYYY'))"} >}price)