topic Re: Matching Logic in QlikView

Matching Logic

tmumaw — Fri, 20 Jul 2018 13:14:23 GMT

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

Re: Matching Logic

marcelviegas — Fri, 20 Jul 2018 13:32:37 GMT

try,

wildmatch

Re: Matching Logic

Anil_Babu_Samineni — Fri, 20 Jul 2018 14:05:13 GMT

Can you explain little more to check?

Re: Matching Logic

tmumaw — Fri, 20 Jul 2018 14:14:44 GMT

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

Re: Matching Logic

marcelviegas — Fri, 20 Jul 2018 14:25:58 GMT

IntervalMatch

Re: Matching Logic

YoussefBelloum — Fri, 20 Jul 2018 14:28:08 GMT

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)..

Re: Matching Logic

tmumaw — Fri, 20 Jul 2018 14:33:55 GMT

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)

Re: Matching Logic

mdmukramali — Fri, 20 Jul 2018 15:14:34 GMT

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;

Re: Matching Logic

tmumaw — Tue, 24 Jul 2018 15:23:01 GMT

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)
;

Re: Matching Logic

tmumaw — Tue, 24 Jul 2018 17:47:44 GMT

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

Re: Matching Logic

marcelviegas — Tue, 24 Jul 2018 18:47:00 GMT

Hello Thomas, Try:

Inner Join IntervalMatch (ZWDATE)

load StartDate,EndDate

resident ZTRBLRATES;

Re: Matching Logic

tmumaw — Tue, 24 Jul 2018 18:58:47 GMT

It works, but I get SSyn on RateKey and zwdate

Re: Matching Logic

marcelviegas — Tue, 24 Jul 2018 19:15:24 GMT

tries to concatenate the tables or delete the ones that will not be used.

Re: Matching Logic

marcelviegas — Tue, 24 Jul 2018 19:15:43 GMT

drop table.

Re: Matching Logic

tmumaw — Thu, 26 Jul 2018 11:53:44 GMT

Hi Marcel,

This is very close to working. Thanks for all your help on it. I have one more question. How can I calculate the dollar value based on ZRates * ZHours? Tried different things with no luck.

ZTREQP_EMP:
LOAD Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
date(floor(weekend(ZW_DateEqp, 0, -1)),'YYYY-MM-DD') as ZWDATE,
ZHOURS.ZTREQP as ZHours
FROM

(qvd);

ZTREMP:
Concatenate
LOAD Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
date(floor(weekend(ZW_DateEmp, 0, -1)),'YYYY-MM-DD') as ZWDATE,
ZHOURS.ZTREMP as ZHours
FROM

(qvd);

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

(qvd)
;

Inner Join IntervalMatch (ZWDATE)

load StartDate,EndDate

resident ZTRBLRATES;

Re: Matching Logic

marcelviegas — Thu, 26 Jul 2018 14:01:19 GMT

I believe that multiplying the value (dollar) by ZHours

Re: Matching Logic

marcelviegas — Thu, 26 Jul 2018 14:01:51 GMT

What did you try to do?

Re: Matching Logic

tmumaw — Thu, 26 Jul 2018 14:07:16 GMT

Here is what I have done so far:

ZTREQP_EMP:
LOAD
Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
date(floor(weekend(ZW_DateEqp, 0, -1)),'YYYY-MM-DD') as ZWDATE,
ZHOURS.ZTREQP as ZHours
FROM

(qvd);

ZTREMP:
Concatenate
LOAD
Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
date(floor(weekend(ZW_DateEmp, 0, -1)),'YYYY-MM-DD') as ZWDATE,
ZHOURS.ZTREMP as ZHours
FROM

(qvd);

ZTRHours:
Load
Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
RateKey,
ZWDATE,
Sum(ZHours) as SHours
Resident ZTREQP_EMP
Group by
%ZTICKNO,
Type_Ind,
Ticket_Type,
BillCode,
RateKey,
ZWDATE
;

Drop Table ZTREQP_EMP
;

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

(qvd)
;

Inner Join IntervalMatch (ZWDATE)

load StartDate,EndDate

resident ZTRBLRATES;

Exit Script;

Here is what I got out of it.

Then I tried to create a straight table and got this when I took SHours * Rates:

Then I added Bill_Code to it and got the right answer, but I don't want to have to include Bill_Code.