IntervalMatch

[qlikviewaf]

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!

[ankitaag]

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

[sunny_talwar]

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;

[qlikviewaf]

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

[ankitaag]

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;

[timanshu]

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.

[ankitaag]

Hi Timanshu,

Sure!!

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

[timanshu]

Hi Ankita,

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

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