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
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;
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
Hi
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