Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
LOAD
SalesOrderID,
RevisionNumber,
OrderDate,
Status,
OnlineOrderFlag,
SalesOrderNumber,
PurchaseOrderNumber,
AccountNumber as SalesOrderHeader_AccountNumber,
CustomerID,
TerritoryID as SalesOrderHeader_TerritoryID ,
ShipToAddressID as AddressID,
ShipMethodID,
TaxAmt,
Freight,
LocalCurrency,
LOAD F1,
[275],
[276],
[277],
[279],
[280],
[282],
[284],
[286],
[288],
[289],
[290],
Website
FROM
[..\Certification\DeveloperCertificationV10Document\Data\Certification Data.xlsx]
(ooxml, embedded labels, table is Quota);
in second table , F1 is quarter date and 275 to 290 salesperson ID . Now I have to connect it with first table and find out the monthly quota. Please help me
Can you provide few lines of raw data with your expected output?
These values have no selections made. All dates are Calendar, not Fiscal. All momentary values are USD. | ||||||||||||||||||||||||
2006-Q3 | 2006-Q4 | 2007-Q1 | 2007-Q2 | 2007-Q3 | 2007-Q4 | 2008-Q1 | 2008-Q2 | 2008-Q3 | 2008-Q4 | 2009-Q1 | 2009-Q2 | |||||||||||||
Salesperson | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota |
Ansman-Wolfe, Pamela O. | $618,446 | $700,000 | $1,071,344 | $665,000 | $859,414 | $665,000 | $706,047 | $770,000 | $1,014,775 | $805,000 | $248,004 | $765,000 | - | - | - | - | - | - | - | - | - | - | - | - |
Blythe, Michael G. | $217,471 | $200,000 | $351,075 | $190,000 | $287,219 | $190,000 | $327,156 | $220,000 | $982,660 | $580,000 | $846,866 | $550,000 | $561,693 | $550,000 | $696,189 | $638,000 | $943,545 | $667,000 | $804,164 | $635,000 | $624,232 | $635,000 | $699,305 | $735,000 |
Carson, Jillian . | $344,711 | $400,000 | $606,530 | $380,000 | $465,867 | $380,000 | $607,068 | $440,000 | $834,132 | $775,000 | $718,572 | $735,000 | $669,686 | $735,000 | $853,016 | $853,000 | $750,814 | $850,000 | $590,423 | $800,000 | $475,330 | $800,000 | $581,127 | $935,000 |
Mensa-Annan, Tete A. | - | - | - | - | - | - | - | - | - | - | $642,859 | $665,000 | $682,808 | $665,000 | $798,513 | $770,000 | $1,717,249 | $1,400,000 | $1,441,965 | $1,330,000 | $1,164,712 | $1,330,000 | $1,468,939 | $1,540,000 |
Mitchell, Linda C. | $795,748 | $850,000 | $1,097,830 | $808,000 | $1,010,045 | $808,000 | $985,617 | $935,000 | $2,410,670 | $1,600,000 | $1,859,674 | $1,520,000 | $1,353,316 | $1,520,000 | $1,818,366 | $1,750,000 | $2,197,166 | $1,850,000 | $1,762,925 | $1,750,000 | $1,439,789 | $1,750,000 | $1,735,314 | $2,035,000 |
Pak, Jae B. | - | - | - | - | - | - | - | - | $1,860,335 | $1,250,000 | $1,396,217 | $1,175,000 | $1,008,618 | $1,175,000 | $1,349,999 | $1,375,000 | $1,744,784 | $1,400,000 | $1,547,904 | $1,330,000 | $1,024,146 | $1,330,000 | $1,366,116 | $1,540,000 |
Reiter, Tsvi Michael. | $579,275 | $550,000 | $869,647 | $525,000 | $670,885 | $525,000 | $737,241 | $605,000 | $781,884 | $630,000 | $625,893 | $600,000 | $477,543 | $600,000 | $594,869 | $693,000 | $741,878 | $725,000 | $614,990 | $680,000 | $500,264 | $680,000 | $673,047 | $795,000 |
Saraiva, José Edvaldo. | $637,983 | $700,000 | $875,377 | $665,000 | $775,755 | $665,000 | $790,692 | $770,000 | $471,783 | $450,000 | $369,975 | $425,000 | $273,907 | $425,000 | $290,828 | $495,000 | $862,227 | $660,000 | $733,185 | $627,000 | $542,464 | $627,000 | $734,641 | $726,000 |
Tsoflias, Lynn N. | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | $450,884 | $450,000 | $396,547 | $425,000 | $340,479 | $425,000 | $406,425 | $495,000 |
Valdez, Rachel B. | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | $571,523 | $450,000 | $544,651 | $425,000 | $405,962 | $425,000 | $498,959 | $495,000 |
Varkey Chudukatil, Ranjit R. | - | - | - | - | - | - | - | - | $523,999 | $350,000 | $333,124 | $330,000 | $238,773 | $330,000 | $332,125 | $385,000 | $957,497 | $725,000 | $845,410 | $685,000 | $597,368 | $685,000 | $779,243 | $795,000 |
Website Sales | $1,453,523 | $1,500,000 | $1,812,851 | $1,425,000 | $1,791,698 | $1,425,000 | $2,014,012 | $1,650,000 | $1,396,834 | $1,500,000 | $1,327,799 | $1,425,000 | $1,413,530 | $1,425,000 | $1,623,971 | $1,650,000 | $2,744,340 | $2,750,000 | $4,009,218 | $3,450,000 | $4,283,630 | $4,300,000 | $5,436,429 | $5,375,000 |
Total | $4,647,157 | $4,900,000 | $6,684,652 | $4,658,000 | $5,860,884 | $4,658,000 | $6,167,833 | $5,390,000 | $10,277,073 | $7,940,000 | $8,368,983 | $8,190,000 | $6,679,874 | $7,425,000 | $8,357,875 | $8,609,000 | $13,681,907 | $11,927,000 | $13,291,381 | $12,137,000 | $11,398,376 | $12,987,000 | $14,379,545 | $15,466,000 |
These values have no selections made. All dates are Calendar, not Fiscal. All momentary values are USD. | ||||||||||||||||||||||||
2006-Q3 | 2006-Q4 | 2007-Q1 | 2007-Q2 | 2007-Q3 | 2007-Q4 | 2008-Q1 | 2008-Q2 | 2008-Q3 | 2008-Q4 | 2009-Q1 | 2009-Q2 | |||||||||||||
Salesperson | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota | Sales | Quota |
Ansman-Wolfe, Pamela O. | $119,807 | $133,333 | $211,503 | $126,667 | $143,953 | $126,667 | $447,778 | $146,667 | $763,213 | $690,000 | $999,151 | $806,786 | - | - | - | - | - | - | - | - | - | - | - | - |
Blythe, Michael G. | ($17,061) | ($16,667) | ($20,904) | ($15,833) | ($52,820) | ($15,833) | $398,288 | ($18,333) | $672,892 | $675,000 | $1,034,849 | $807,321 | $586,534 | $790,714 | $745,841 | $918,571 | $5,481,588 | $4,858,742 | $5,727,172 | $5,124,288 | $5,118,842 | $5,652,318 | $6,495,528 | $6,802,258 |
Carson, Jillian . | ($153,929) | ($166,667) | ($253,311) | ($158,333) | ($249,594) | ($158,333) | $348,799 | ($183,333) | $582,570 | $660,000 | $1,070,548 | $807,857 | $553,611 | $785,893 | $703,666 | $913,429 | $5,974,091 | $5,300,421 | $6,267,470 | $5,601,435 | $5,614,090 | $6,188,906 | $7,126,134 | $7,452,195 |
Mensa-Annan, Tete A. | - | - | - | - | - | - | - | - | - | - | $1,106,246 | $808,393 | $520,688 | $781,071 | $661,492 | $908,286 | $6,466,594 | $5,742,099 | $6,807,769 | $6,078,582 | $6,109,338 | $6,725,493 | $7,756,740 | $8,102,132 |
Mitchell, Linda C. | $795,748 | $850,000 | $1,097,830 | $808,000 | $1,010,045 | $808,000 | $985,617 | $935,000 | ($342,610) | ($35,000) | $1,141,945 | $808,929 | $487,766 | $776,250 | $619,317 | $903,143 | $6,959,098 | $6,183,777 | $7,348,068 | $6,555,728 | $6,604,586 | $7,262,080 | $8,387,345 | $8,752,069 |
Pak, Jae B. | - | - | - | - | - | - | - | - | ($1,032,121) | ($442,000) | $1,177,643 | $809,464 | $454,843 | $771,429 | $577,142 | $898,000 | $7,451,601 | $6,625,456 | $7,888,366 | $7,032,875 | $7,099,834 | $7,798,668 | $9,017,951 | $9,402,006 |
Reiter, Tsvi Michael. | $696,691 | $850,000 | $881,107 | $805,000 | $880,626 | $805,000 | $844,143 | $935,000 | ($1,721,632) | ($849,000) | $1,213,342 | $810,000 | $421,920 | $766,607 | $534,968 | $892,857 | $7,944,104 | $7,067,134 | $8,428,665 | $7,510,022 | $7,595,082 | $8,335,255 | $9,648,557 | $10,051,943 |
Saraiva, José Edvaldo. | $755,399 | $1,000,000 | $886,837 | $945,000 | $985,496 | $945,000 | $897,593 | $1,100,000 | ($2,411,143) | ($1,256,000) | $1,249,040 | $810,536 | $388,997 | $761,786 | $492,793 | $887,714 | $8,436,607 | $7,508,812 | $8,968,964 | $7,987,169 | $8,090,330 | $8,871,843 | $10,279,163 | $10,701,880 |
Tsoflias, Lynn N. | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | $8,929,110 | $7,950,491 | $9,509,262 | $8,464,316 | $8,585,578 | $9,408,430 | $10,909,768 | $11,351,817 |
Valdez, Rachel B. | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | $9,421,613 | $8,392,169 | $10,049,561 | $8,941,463 | $9,080,826 | $9,945,017 | $11,540,374 | $12,001,754 |
Varkey Chudukatil, Ranjit R. | - | - | - | - | - | - | - | - | $13,819,042 | $10,853,333 | $11,379,161 | $11,175,000 | $9,218,493 | $10,155,000 | $11,463,740 | $11,772,000 | $9,914,116 | $8,833,847 | $10,589,860 | $9,418,610 | $9,576,074 | $10,481,605 | $12,170,980 | $12,651,691 |
Website Sales | $7,840,791 | $8,300,000 | $11,556,453 | $7,891,000 | $9,930,071 | $7,891,000 | $10,321,653 | $9,130,000 | $18,695,579 | $14,648,333 | $15,397,091 | $15,105,000 | $12,439,044 | $13,702,500 | $15,476,616 | $15,884,000 | $10,406,619 | $9,275,526 | $11,130,158 | $9,895,756 | $10,071,322 | $11,018,192 | $12,801,585 | $13,301,628 |
Total | $11,034,425 | $11,700,000 | $16,428,255 | $11,124,000 | $13,999,257 | $11,124,000 | $14,475,473 | $12,870,000 | $23,572,116 | $18,443,333 | $19,415,020 | $19,035,000 | $15,659,595 | $17,250,000 | $19,489,491 | $19,996,000 | $10,899,123 | $9,717,204 | $11,670,457 | $10,372,903 | $10,566,570 | $11,554,780 | $13,432,191 | $13,951,565 |
Total | $962,717 | $2,044,600 | $1,639,840 | $1,358,050 | $2,868,129 | $2,458,472 | $1,309,863 | $2,451,606 | $2,099,416 | $1,546,592 | $2,942,673 | $1,678,567 | $2,894,055 | $4,147,192 | $3,235,826 | $2,217,544 | $3,388,911 | $2,762,527 | $1,756,407 | $2,873,937 | $2,049,530 | $2,371,678 | $3,443,525 | $2,542,672 |
Your first table doesn't contain a field that contains the salesperson ID. The second table doesn't contain information about sales orders. That means there is now way to associate the tables properly. Your tables are not related at all.