Intervalmatch results issue

[#1]

Hi

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

[Chanty4u]

try with givng name as  PolicyProductSales

Inner join (Locations)

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

[Kushal_Chawda]

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

[#4]

Hi

In the PolicyProductSales not containing the columns startdate and enddate

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

Madhu

[#5]

Hi

In the PolicyProductSales not containing the columns startdate and enddate

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

Madhu

[Digvijay_Singh]

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;

[#7]

need to use intervalmatch ptrefix

thanks

Madhu

[Digvijay_Singh]

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;

[Kushal_Chawda]

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.

[jagan]

HI,

Check this link

IntervalMatch

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

Regards,

Jagan.