Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try with givng name as PolicyProductSales
Inner join (Locations)
IntervalMatch('salesdate') LOAD startdate,enddate Resident PolicyProductSales;
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]);
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
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
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;
need to use intervalmatch ptrefix
thanks
Madhu
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;
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.
HI,
Check this link
You need to use Extended IntervalMatch() , check qlikview help file for more details.
Regards,
Jagan.