Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Intervalmatch results issue

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

  

Salespersonidsalesdatesalesamount
1010/3/201510000
2020/03/201520000
3025/03/2015

30000

   

Salespersonidsalespersonlocationsstartdateenddate
10alondon1/3/201530/03/2015
20bmalasiya15/03/201522/03/2015
30cindia20/03/201528/03/2015
10amalasiya1/3/201520/03/2015
20bindia15/03/201523/03/2015
30clondon20/03/201529/03/2015

MY RESULT TABLE:

     

enddatelocationssalesamountsalespersonSalespersonidstartdate
20/03/2015malasiya10000a101/3/2015
22/03/2015malasiyab2015/03/2015
23/03/2015indiab2015/03/2015
28/03/2015indiac3020/03/2015
29/03/2015londonc3020/03/2015
30/03/2015london10000a101/3/2015
2000020
3000030

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

12 Replies
Chanty4u
Champion III
Champion III

try with givng name as  PolicyProductSales

Inner join (Locations)

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

Kush
MVP
MVP

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

Not applicable
Author

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

Not applicable
Author

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
Master III
Master III

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;

Not applicable
Author

need to use intervalmatch ptrefix

thanks

Madhu

Digvijay_Singh
Master III
Master III

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;

Kush
MVP
MVP

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

HI,

Check this link

IntervalMatch

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

Regards,

Jagan.