topic Re: Intervalmatch results issue in QlikView

Intervalmatch results issue

Wed, 25 Nov 2020 16:16:04 GMT

i load the data like this

PolicyProductSales:

LOAD Salespersonid,

salesdate,

salesamount

FROM

[..\14564.xlsx]

(ooxml, embedded labels, table is [policyproduct sales]);

Locations:

LOAD Salespersonid,

salesperson,

locations,

startdate,

enddate

FROM

[..\14564.xlsx]

(ooxml, embedded labels, table is offices);

Inner join (Locations)

IntervalMatch('salesdate') LOAD startdate,enddate Resident Locations;

This is my data

Salespersonid	salesdate	salesamount
10	10/3/2015	10000
20	20/03/2015	20000
30	25/03/2015	30000

Salespersonid	salesperson	locations	startdate	enddate
10	a	london	1/3/2015	30/03/2015
20	b	malasiya	15/03/2015	22/03/2015
30	c	india	20/03/2015	28/03/2015
10	a	malasiya	1/3/2015	20/03/2015
20	b	india	15/03/2015	23/03/2015
30	c	london	20/03/2015	29/03/2015

MY RESULT TABLE:

enddate	locations	salesamount	salesperson	Salespersonid	startdate
20/03/2015	malasiya	10000	a	10	1/3/2015
22/03/2015	malasiya		b	20	15/03/2015
23/03/2015	india		b	20	15/03/2015
28/03/2015	india		c	30	20/03/2015
29/03/2015	london		c	30	20/03/2015
30/03/2015	london	10000	a	10	1/3/2015
		20000		20
		30000		30

Is this correct result?

'a' located in two counties his sales only 10000 how it takes in locations sales and what about 'b'

can any help me

Thanks

Madhu

Re: Intervalmatch results issue

Chanty4u — Thu, 10 Mar 2016 06:14:53 GMT

try with givng name as PolicyProductSales

Inner join (Locations)

IntervalMatch('salesdate') LOAD startdate,enddate Resident PolicyProductSales;

Re: Intervalmatch results issue

Kushal_Chawda — Thu, 10 Mar 2016 06:22:26 GMT

try this

Locations:

LOAD Salespersonid,

salesperson,

locations,

startdate ,

date(startdate + iterno()-1) as salesdate,

enddate

FROM

[..\14564.xlsx]

(ooxml, embedded labels, table is offices)

while startdate + iterno()-1 <=enddate ;

inner join (Locations)

LOAD Salespersonid,

date(salesdate) as salesdate,

salesamount

FROM

[..\14564.xlsx]

(ooxml, embedded labels, table is [policyproduct sales]);

Re: Intervalmatch results issue

Thu, 10 Mar 2016 06:29:06 GMT

In the PolicyProductSales not containing the columns startdate and enddate

if we use the policy Product sales table it load only two table.

Madhu

Re: Intervalmatch results issue

Thu, 10 Mar 2016 06:31:35 GMT

In the PolicyProductSales not containing the columns startdate and enddate

if we use the policy Product sales table it load only two table.

Madhu

Re: Intervalmatch results issue

Digvijay_Singh — Thu, 10 Mar 2016 06:36:54 GMT

Seems you are missing the key element in interval join, see this sample -

SET FirstMonthOfYear=1;

SET CollationLocale='en-IN';

PolicyProductSales:

LOAD Salespersonid,

salesdate,

salesamount

inline [

Salespersonid, salesdate, salesamount,

10, 10/3/2015, 10000

20, 20/03/2015, 20000

30, 25/03/2015, 30000 ];

Locations:

LOAD Salespersonid,

salesperson,

locations,

startdate,

enddate

inline [

Salespersonid, salesperson, locations, startdate, enddate,

10, a, london, 1/3/2015, 30/03/2015

20, b, malasiya, 15/03/2015, 22/03/2015

30, c, india, 20/03/2015, 28/03/2015

10, a, malasiya, 1/3/2015, 20/03/2015

20, b, india, 15/03/2015, 23/03/2015

30, c, london, 20/03/2015, 29/03/2015 ];

IntervalMatch:

IntervalMatch('salesdate',Salespersonid)

LOAD startdate,enddate,Salespersonid

Resident Locations;

Inner Join(IntervalMatch)

Load *

Resident Locations;

Drop Table Locations,PolicyProductSales;

Re: Intervalmatch results issue

Thu, 10 Mar 2016 06:38:42 GMT

need to use intervalmatch ptrefix

thanks

Madhu

Re: Intervalmatch results issue

Digvijay_Singh — Thu, 10 Mar 2016 06:51:14 GMT

make this small change in your script starting from inner join statement, it should work -

inner join (PolicyProductSales)

IntervalMatch('salesdate',Salespersonid)

LOAD startdate,enddate,Salespersonid

Resident Locations;

Drop Table Locations;

Re: Intervalmatch results issue

Kushal_Chawda — Thu, 10 Mar 2016 06:53:21 GMT

Interval match & the method which I have shown will give you the same result. Instead the method which I have shown will be faster than Interval match.

Re: Intervalmatch results issue

jagan — Thu, 10 Mar 2016 07:05:21 GMT

HI,

Check this link

IntervalMatch

You need to use Extended IntervalMatch() , check qlikview help file for more details.

Regards,

Jagan.

Re: Intervalmatch results issue

tamilarasu — Thu, 10 Mar 2016 07:50:24 GMT

HI,

Check the below script.

Data:
LOAD Salespersonid,
     salesdate,
     salesamount
FROM
[..\Desktop\Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

Locations:
LOAD Salespersonid,
     salesperson,
     locations,
     startdate,
     enddate
FROM
[..\Desktop\Book1.xlsx]
(ooxml, embedded labels, table is Sheet2); 

Inner Join 
INTERVALMATCH (salesdate, Salespersonid)
LOAD
startdate,
enddate,
Salespersonid
RESIDENT Locations;

Left join(Data)
Load * Resident Locations;

Drop Table Locations;

Re: Intervalmatch results issue

sasikanth — Thu, 10 Mar 2016 08:21:00 GMT

HI,

Check your location table once

10, a, london, 1/3/2015, 30/03/2015

10, a, malasiya, 1/3/2015, 20/03/2015

Can u tell me the meaning of this i.e. for ID =10, startdate is same for both the locations ?

because of this the salesdate fall in between these two , this might be the reason

Re: Intervalmatch results issue

Thu, 10 Mar 2016 08:39:54 GMT

yes I give same dates for 2 locations the result should show only sales person 'a'

but what about 'b' and 'c' results i didn't understand it evaluate

Thanks

Madhu