Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II

Matching Logic

Good morning everyone,

I have a question to ask everyone.  I have 2 QVD's one has detailed information by PO Type, PO Key, Billing Code and date, then the other QVD(Pricing) has PO type, PO key, Billing code and date.  The problem is the QVD(Pricing) can have multiple prices for different price range dates.  I need to match my detail file with my pricing file and be sure and use the correct price to that time period.  Hope this makes since.  Thanks

17 Replies
marcelviegas
Creator II
Creator II

try,

wildmatch

Anil_Babu_Samineni

Can you explain little more to check?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
tmumaw
Specialist II
Specialist II
Author

I will try.  I have a Orders file which has Order number and products to order and the order date.  On the other file I have my pricing by product, date and price.  The pricing file can have multiple entries for the same product only for different dates.  I have to match the key from the orders with the key from the pricing based on dates.  Ex.

Order  xyz

product 123

date 7/20/2018

Pricing

Product     from date    to date       price

123            6/30/2018  7/5/2018      100.00

123            7/6/2018    7/15/2018       98.00

123            7/16/2018  7/25/2018     150.00        (I need to use this one)

Hope this helps.

Thanks

marcelviegas
Creator II
Creator II

IntervalMatch

YoussefBelloum
Champion
Champion

Hi,

What is the rule that makes you choose that line ?

You can try to use intervalmatch to match your Order date (from the Order table) to your Pricing Date fields (From_Date, To_Date)..

tmumaw
Specialist II
Specialist II
Author

The product from the order date fill within from and to date range.

Order  xyz

product 123

date 7/20/2018

Pricing

Product    from date    to date      price

123            6/30/2018  7/5/2018      100.00

123            7/6/2018    7/15/2018      98.00

123            7/16/2018  7/25/2018    150.00       (I need to use this one)

mdmukramali
Specialist III
Specialist III

Hi Thomas,

As suggested by others Try Interval match Function,

//Like:

Product:

Load * Inline

[

Order,Product,Date

xyz,123,7/20/2018

xyz,123,6/30/2018

];

Price_Table:

Load * inline

[

Product,from_date, to_date,price

123,6/30/2018,7/5/2018,100.00

123,7/6/2018,7/15/2018,98.00

123,7/16/2018,7/25/2018,150.00

];

Inner Join //(Product)

IntervalMatch (Date,Product)

LOAD from_date, to_date, Product

Resident Price_Table;

tmumaw
Specialist II
Specialist II
Author

Hi Mohammed,

Been out of the office for a few days.  Here is my script.  How would I add the intervalmatch to it?  The bottom table ZTRBLRATES are the different rates based on ZWDATE from above tables....Thanks

ZTREQP_EMP:
LOAD Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
ZWDATE.ZTREQP as ZWDATE
FROM

(
qvd);

ZTREMP:
Concatenate
LOAD Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
ZWDATE.ZTREMP as ZWDATE
FROM

(
qvd);

ZTRBLRATES:
LOAD TICKCLIENT.ZTRBLRATES,
EMPEQP.ZTRBLRATES,
BILLCODE.ZTRBLRATES,
TICKCLIENT.ZTRBLRATES & EMPEQP.ZTRBLRATES & BILLCODE.ZTRBLRATES as RateKey,
StartDate,
EndDate,
BILLRATE.ZTRBLRATES
FROM

(
qvd)

tmumaw
Specialist II
Specialist II
Author

Okay Marcel,

Here is my script and I sure can't figure out the intervalmatch.......maybe you can help.

ZTREQP_EMP:
LOAD Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
ZW_DateEqp as ZWDATE
FROM

(
qvd);

ZTREMP:
Concatenate
LOAD Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
ZW_DateEmp as ZWDATE
FROM

(
qvd);

ZTRBLRATES:
LOAD TICKCLIENT.ZTRBLRATES,
EMPEQP.ZTRBLRATES,
BILLCODE.ZTRBLRATES,
TICKCLIENT.ZTRBLRATES & EMPEQP.ZTRBLRATES & BILLCODE.ZTRBLRATES as RateKey,
StartDate,
EndDate,
BILLRATE.ZTRBLRATES
FROM

(
qvd)
;


Final_Table:
IntervalMatch(ZWDATE,RateKey)
Left Join (ZTREQP_EMP)
Load
StartDate,
EndDate,
BILLRATE.ZTRBLRATES
Resident ZTRBLRATES;

Left Join (ZTREQP_EMP)
Load Distinct *
Resident ZTRBLRATES;

Drop table ZTRBLRATES; 

ZWDATE does not seem to fall within the start and end date ranges.

Thanks