Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Price record between two dates

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**

Screenshot_1.png

4 Replies
sunny_talwar
Not applicable

Re: Price record between two dates

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

Not applicable

Re: Price record between two dates

Price_list

     

ItemItem_codepriceprice.startprice.exp
ITEM_NAME_101022133,072010-04-12 00:00:00.0002014-04-01
ITEM_NAME_201510037,472010-04-12 00:00:00.0002014-12-01
ITEM_NAME_301050033,972014-04-12 00:00:00.0002016-01-01
ITEM_NAME_407152063,472010-04-12 00:00:00.0002014-05-31
ITEM_NAME_506020063,182013-04-12 00:00:00.000
ITEM_NAME_201510039,992014-12-02 00:00:00.000
ITEM_NAME_407152064,47

2014-06-01 00:00:00.000

Sales

      

IDItem_codeClient_IDDatekgprice
189120102213DAR0022014-01-0212,0001,45
189131115102IND0152015-01-0210,0002,29
189140105003DAI000122015-05-022,61,56
189150715206DAI000072015-01-021309,0001,1
189160602006DAI000122016-03-020,8854,08
189170151003DAI000122015-01-021342,0001,35
Gysbert_Wassenaar
Not applicable

Re: Price record between two dates

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;


talk is cheap, supply exceeds demand
pulkit1611
Not applicable

Re: Price record between two dates

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)