experts dealing with huge data needed please

[Invader]

Hi all,

i have 1 year sales data which exceeds 5 million rows, my issues are as follows

- each row is in a different currency which i need to convert them all based on monthly rate (i have a monthly rate sheet)
- i need to combine them all and use 1 pivot table
- the data is heavy please help
- i need a column for total tax which is : the sum of (Ads Taxa1, Ads Taxa2, Ads Taxa3) without the alphabetical codes and i use "Ads Totl" currency code (which is the first 3 letters of the "Ads Totl" cell)
- "Ads Fare" cell have their own currency which i separate by taking the first three letter of the cell, "Ads Totl" cell i do the same.

"below is few rows example of the data that i have"

please help me urgently

[TABLE="width: 959"]
<colgroup><col span="2"><col><col><col span="3"><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD]Bas Pnrr Pnr[/TD]
[TD]Bas Agtn No[/TD]
[TD]Ticket_Number[/TD]
[TD]Ads Fare[/TD]
[TD]Ads Taxa 1[/TD]
[TD]Ads Taxa 2[/TD]
[TD]Ads Taxa 3[/TD]
[TD]Ads Totl[/TD]
[TD]Itn Clsc 1[/TD]
[TD]Itn Clsc 2[/TD]
[TD]Bas Dais Dt[/TD]
[TD]Ads Tour[/TD]
[TD]Ads Orin[/TD]
[/TR]
[TR]
[TD]EVLRQO[/TD]
[TD]8621040[/TD]
[TD="align: right"]9181414188[/TD]
[TD]BHD 38.000[/TD]
[TD] 5ZR[/TD]
[TD] 50BH[/TD]
[TD] 10HM[/TD]
[TD]AED 445[/TD]
[TD]N[/TD]
[TD][/TD]
[TD="align: right"]05-Oct-16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FEIPDJ[/TD]
[TD]8620997[/TD]
[TD="align: right"]9181407103[/TD]
[TD]BHD 38.000[/TD]
[TD] 5ZR[/TD]
[TD] 50BH[/TD]
[TD] 10HM[/TD]
[TD]AED 445[/TD]
[TD]N[/TD]
[TD][/TD]
[TD="align: right"]05-Oct-16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JHZRWV[/TD]
[TD]8621040[/TD]
[TD="align: right"]9181414191[/TD]
[TD]BHD 38.000[/TD]
[TD] 5ZR[/TD]
[TD] 50BH[/TD]
[TD] 10HM[/TD]
[TD]AED 445[/TD]
[TD]N[/TD]
[TD][/TD]
[TD="align: right"]05-Oct-16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XTJDNB[/TD]
[TD]8649029[/TD]
[TD="align: right"]2428450116[/TD]
[TD]AED 500[/TD]
[TD] 40DV[/TD]
[TD] 100YQ[/TD]
[TD] 180XT[/TD]
[TD]AED 820[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD="align: right"]05-Oct-16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XTJDNB[/TD]
[TD]8649029[/TD]
[TD="align: right"]2428450117[/TD]
[TD]AED 500[/TD]
[TD] 40DV[/TD]
[TD] 100YQ[/TD]
[TD] 180XT[/TD]
[TD]AED 820[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD="align: right"]05-Oct-16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JTBJNO[/TD]
[TD]8649029[/TD]
[TD="align: right"]2428450108[/TD]
[TD]AED 510[/TD]
[TD] 40DV[/TD]
[TD] 400YQ[/TD]
[TD] 280XT[/TD]
[TD]AED 1230[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD="align: right"]05-Oct-16[/TD]
[TD]IT0116DXBCPCGF1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[CharlesChuckieCharles]

What columns of data do you want ( it sounds like Ticket Number can be ignored?)


how do you want it consolidated? i.e Rolled up into Month Currency entires or how

Date, Curr, Total value, TotalTax
OCT 2016, BHD, 309, 195


Need to know How you want it to look

 

[Invader]

Hi, thanks for your reply

i would like to add few columns to the raw data

- to convert all currencies to BHD
- add three more columns to show look up value based on Bas Agnt No

[TABLE="width: 658"]
<tbody>[TR]
[TD]Fare BHD[/TD]
[TD]Tax BHD[/TD]
[TD]Total BHD[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]POS City Code[/TD]
[TD]POS Agent Name[/TD]
[TD]Category Code[/TD]
[/TR]
[TR]
[TD]###.###[/TD]
[TD]###.###[/TD]
[TD]###.###[/TD]
[TD]Jan[/TD]
[TD="align: right"]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


than in the end i want to have two tables one to show the agents sales by month

something like the below

[TABLE="width: 1157"]
<colgroup><col><col><col><col span="5"><col></colgroup><tbody>[TR]
[TD]City[/TD]
[TD]Category Code[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]WGF[/TD]
[TD]Area Office[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WGF[/TD]
[TD]EB2 BOOKING ENGINE - BAH[/TD]
[TD="align: right"]3,245,729[/TD]
[TD="align: right"]2,890,895[/TD]
[TD="align: right"]3,918,424[/TD]
[TD="align: right"]3,696,303[/TD]
[TD="align: right"]3,423,511[/TD]
[TD="align: right"]3,643,393[/TD]
[TD="align: right"]20,818,255[/TD]
[/TR]
[TR]
[TD]WGF[/TD]
[TD]BACKUP ONLINE SALES - BAH[/TD]
[TD="align: right"]10,164[/TD]
[TD="align: right"]4,640[/TD]
[TD="align: right"]8,776[/TD]
[TD="align: right"]9,571[/TD]
[TD="align: right"]8,077[/TD]
[TD="align: right"]6,444[/TD]
[TD="align: right"]47,671[/TD]
[/TR]
[TR]
[TD]WGF Total[/TD]
[TD][/TD]
[TD="align: right"]3,255,893[/TD]
[TD="align: right"]2,895,534[/TD]
[TD="align: right"]3,927,200[/TD]
[TD="align: right"]3,705,874[/TD]
[TD="align: right"]3,431,587[/TD]
[TD="align: right"]3,649,837[/TD]
[TD="align: right"]20,865,926[/TD]
[/TR]
[TR]
[TD]BAH[/TD]
[TD]Area Office[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BAH[/TD]
[TD]GULF AIR SALES OFFICE[/TD]
[TD="align: right"]718,283[/TD]
[TD="align: right"]555,603[/TD]
[TD="align: right"]643,944[/TD]
[TD="align: right"]807,479[/TD]
[TD="align: right"]2,470,167[/TD]
[TD="align: right"]761,774[/TD]
[TD="align: right"]5,957,250[/TD]
[/TR]
[TR]
[TD]BAH[/TD]
[TD]STAFF TRAVEL SALES OFFICE[/TD]
[TD="align: right"]119,061[/TD]
[TD="align: right"]88,450[/TD]
[TD="align: right"]145,989[/TD]
[TD="align: right"]138,796[/TD]
[TD="align: right"]135,659[/TD]
[TD="align: right"]123,687[/TD]
[TD="align: right"]751,642[/TD]
[/TR]
[TR]
[TD]BAH[/TD]
[TD]HDQ - MYID TRAVEL[/TD]
[TD="align: right"]22,842[/TD]
[TD="align: right"]21,682[/TD]
[TD="align: right"]28,560[/TD]
[TD="align: right"]26,823[/TD]
[TD="align: right"]20,694[/TD]
[TD="align: right"]19,985[/TD]
[TD="align: right"]140,587[/TD]
[/TR]
[TR]
[TD]BAH[/TD]
[TD]STNME-STAFF TRAVEL[/TD]
[TD="align: right"]1,123[/TD]
[TD="align: right"]638[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,761[/TD]
[/TR]
[TR]
[TD]BAH[/TD]
[TD]WEB B2B BH[/TD]
[TD][/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]256[/TD]
[TD][/TD]
[TD="align: right"]281[/TD]
[TD="align: right"]526[/TD]
[TD="align: right"]1,175[/TD]
[/TR]
[TR]
[TD]BAH[/TD]
[TD]BAH-BAHRAIN-SEEF (SQMS HDQ QUOTA)[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]BAH[/TD]
[TD]Airport Office[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BAH[/TD]
[TD]GULF AIR SALES OFFICE[/TD]
[TD="align: right"]172,146[/TD]
[TD="align: right"]121,872[/TD]
[TD="align: right"]158,696[/TD]
[TD="align: right"]156,991[/TD]
[TD="align: right"]166,641[/TD]
[TD="align: right"]173,092[/TD]
[TD="align: right"]949,438[/TD]
[/TR]
[TR]
[TD]BAH Total[/TD]
[TD][/TD]
[TD="align: right"]1,033,455[/TD]
[TD="align: right"]788,376[/TD]
[TD="align: right"]977,445[/TD]
[TD="align: right"]1,130,089[/TD]
[TD="align: right"]2,793,442[/TD]
[TD="align: right"]1,079,064[/TD]
[TD="align: right"]7,801,871[/TD]
[/TR]
</tbody>[/TABLE]


lookup will be taken from a table like the below

[TABLE="width: 858"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]POS Agent Code[/TD]
[TD]POS City Code[/TD]
[TD]POS Agent Name[/TD]
[TD]Category Code[/TD]
[TD]POS Country Code[/TD]
[/TR]
[TR]
[TD]8621320[/TD]
[TD]AAN[/TD]
[TD]AKBAR TRAVELS OF INDIA GULF LL[/TD]
[TD]BSP[/TD]
[TD]AE[/TD]
[/TR]
[TR]
[TD]8624424[/TD]
[TD]AAN[/TD]
[TD]AL AIN EXPRESS TVL[/TD]
[TD]BSP[/TD]
[TD]AE[/TD]
[/TR]
[TR]
[TD]8620013[/TD]
[TD]AAN[/TD]
[TD]AL AMAAN TVL & TRSM[/TD]
[TD]BSP[/TD]
[TD]AE[/TD]
[/TR]
[TR]
[TD]8621498[/TD]
[TD]AAN[/TD]
[TD]AL DAAR TRAVEL[/TD]
[TD]BSP[/TD]
[TD]AE[/TD]
[/TR]
[TR]
[TD]8621151[/TD]
[TD]AAN[/TD]
[TD]AL-ETIHAD TRAVEL AND TOURISM[/TD]
[TD]BSP[/TD]
[TD]AE[/TD]
[/TR]
[TR]
[TD]8621353[/TD]
[TD]AAN[/TD]
[TD]ALHADAF TRAVEL L.L.C[/TD]
[TD]BSP[/TD]
[TD]AE[/TD]
[/TR]
</tbody>[/TABLE]



and yes ticket number column is important to remove duplicates and to have a count of tickets sold

waiting for your reply
thanks

 

[Invader]

can someone help please !!!