topic Re: IntervalMatch in QlikView

IntervalMatch

qlikviewaf — Wed, 22 Nov 2017 11:24:39 GMT

Hi Guys,

i started to use interval-match to link the invoice table with the sales rep table.

I would like to assign to the invoice table a sales rep using as keyfield the customer id and the Invoice date (based on the range start-end).

I used this statement IntervalMatch (InvoiceDate,CustomerID) LOAD Start, End Resident SalesRep; but i got duplicates then as shown in the attachement example. Where i'm doing wrong?

i expect of course to have just 2 record as invoice!
Thank you for the help!

Re: IntervalMatch

ankitaag — Wed, 22 Nov 2017 11:50:09 GMT

Hi Antonio,

PFA the qvw to your answer. Also writing the script below in case you are not able to open the doc.

SalesRep:

LOAD * INLINE [

CustomerID, Start, End, SalesRep

A, 01/01/2014, 02/01/2014, Lecorn

A, 04/01/2014, 06/01/2014, Giorgione

];

Invoice:

LOAD * INLINE [

Amount, CustomerID, InvoiceDate

100, A, 01/01/2014

500, A, 05/01/2014

];

Left join(Invoice)

Intervalmatch(InvoiceDate)

LOAD Start, End

RESIDENT SalesRep;

Left join (Invoice)

LOAD * resident SalesRep;

drop table SalesRep;

Thanks,

Ankita

Re: IntervalMatch

sunny_talwar — Wed, 22 Nov 2017 12:52:07 GMT

I think an Extended Interval Match might be needed here to join on CustomerID besides just Start and End

SalesRep:

LOAD * INLINE [

Start, End, CustomerID, SalesRep

01/01/2014, 02/01/2014, A, Lecorn

04/01/2014, 06/01/2014, A, Giorgione

];

Invoices:

LOAD * INLINE [

InvoiceDate, CustomerID, Amount

01/01/2014, A, 100

05/01/2014, A, 500

];

Left Join (Invoices)

IntervalMatch (InvoiceDate, CustomerID)

LOAD Start,

End,

CustomerID

Resident SalesRep;

Left Join (Invoices)

LOAD *

Resident SalesRep;

DROP Table SalesRep;

Re: IntervalMatch

qlikviewaf — Wed, 22 Nov 2017 14:34:42 GMT

Ankita you are magic! it works.

Now i have just the last challange.

Since the sales-rep info will be loaded by an excel file managed by user, could it be that the user do a typo creating an overlapping situation like the one highlighted below.

There's a way to prevent this and be able to don't duplicate the Invoice lines? (find attached the example with duplication of lines for invoice table)

Thank you! ! !

Re: IntervalMatch

ankitaag — Thu, 28 Dec 2017 10:33:37 GMT

Hi Antonio,

Apologies for late reply!!

Please find below the script and the qvw attached for your above stated query:

TempSalesRep:

LOAD CustomerID, Date#(Start,'MM/DD/YYYY') as Start, Date#(End,'MM/DD/YYYY') as End, SalesRep INLINE [

CustomerID, Start, End, SalesRep

A, 01/01/2014, 04/01/2014, Lecorn

B, 04/01/2014, 06/01/2014, Giorgione

A, 04/01/2014, 06/01/2014, Giorgione

];

SalesRep:

Load CustomerID,

Start as OldStart,

End,

SalesRep,

IF(Peek(End)=Start,Date(AddMonths(Start,1),'MM/DD/YYYY'),Start) as Start

Resident TempSalesRep

order by CustomerID;

Drop table TempSalesRep;

Invoice:

LOAD Amount, CustomerID, Date#(InvoiceDate,'MM/DD/YYYY') as InvoiceDate INLINE [

Amount, CustomerID, InvoiceDate

100, A, 01/01/2014

500, A, 04/01/2014

500, C, 05/01/2014

];

left join(Invoice)

Intervalmatch(InvoiceDate)

LOAD Start, End

RESIDENT SalesRep;

left join (Invoice)

LOAD * resident SalesRep;

drop table SalesRep;

Re: IntervalMatch

timanshu — Thu, 08 Feb 2018 05:48:04 GMT

Hi Ankita,

I would like to connect to you for Job Prospective. May be also I may help you in some way. Please connect to me.
Please don't mind posting this here.

Re: IntervalMatch

ankitaag — Thu, 15 Feb 2018 11:06:47 GMT

Hi Timanshu,

Sure!!

Also, if you find the answer correct, kindly mark it as correct to close the thread.

Re: IntervalMatch

timanshu — Thu, 08 Mar 2018 09:45:39 GMT

Hi Ankita,

I have not created this discussion. Therefore can't close it.

Please send me mail to connect @ timanshu.vir@gmail.com.