i pulling workbook containing table has columns itemcreationdate, , columns (total 28 such columns) begin word "global" in them. want
- pull these "global" columns (including itemcreationdate) sql recordset, add new column called old/new ,
- unpivot them i.e. stack them 1 above other ,
- in next column, list column items along counts.
- their counts derived based on itemcreationdate date <2015 old & >=2015 new
- final output should shown in output sheet.
i have attached sample.xlsx file have shown how have arrive @ output tab starting data sheet. data sheet input want pull recordset , spit out table shown in output sheet. don't want create pivot table cumbersome , data quite lot, , want alternative sql approach, wherein can aggregate data , insert sheet in 1 go.
i not using sql server, cannot use unpivot command or dynamic sql loop thru "global" columns.
basically want form correct sql string like....
dim arrsql variant ...... ...... rs.filter="like global*" ...... arrsql = join(rs.fields, vbcr) strsql = "select [arrsql], iif(year([item creation date])>=year(date())-1,""new"",""old"") [new/old] [data$] group by...." strsql = strsql & " union " & vbcr & _ strsql = strsql & " ...... now, run sql on same recordset reduce columns , required data format.... know above not quite correct, on lines can correct output show in output tab.
can quickly?
edits @a_horse_with_no_name :
see screenshots of sample file:
- data sheet: table input workbook want pull in recordset. see various "global" column headings , items want unpivot.
- this 2 intermediate sheets "new" & "old" have create everytime (that want rid of actually). items found in 2015 or later put in new, whereas rest put in old.
jfyi, formulae manually used in output columns :
c column (new):
=countif(index(new!$a:$d,0,match($a2,new!$1:$1,0)),output!$b2) d column (old):
=countif(index(old!$a:$d,0,match($a2,old!$1:$1,0)),output!$b2) e column (% new):
=output!c2/sum(c$2:c$6) f column (% old):
=output!d2/sum(d$2:d$6) g column (index):
=if(and(e2<=0,f2<=0),0,if(and(e2>0,f2>0),e2/f2,1)) hope helps.
indeed, can run sql queries in ms excel using jet/ace sql engine (a windows .dll file) data store ms access connected default. , such, technology equipped on pcs not restricted 1 office/windows program.
consider following excel vba macro (if using excel on pc) connects ace via ado running union of 3 aggregate sql queries (global vit/calc, global flavours, global flavour group) , conditional new , old counts/percentages. latter percentage column pair required subqueries.
for proper setup, following:
make sure
item creation datein mm-dd-yyyy (us-based) or dd-mm-yyyy (non-us based) date formats not how above screenshot or file has date field formatted.sub formatdates() = 2 2083 range("a" & i) = cdate(range("a" & i)) next end subrun macro in different workbook 1 holding data. below assumes data workbook holds source information in worksheet named
data.- in query-running workbook, create blank worksheet named
resultspopulated query output including column headers.
vba script (two connections available driver (commented out) , provider versions)
option explicit sub runsql() dim cols object, datawbk workbook, datawks worksheet dim lastcol integer, integer, j variant, output variant set cols = createobject("scripting.dictionary") set datawbk = workbooks.open("c:\path\to\data\workbook.xlsx;") set datawks = datawbk.worksheets("data") lastcol = datawks.cells(7, datawks.columns.count).end(xltoleft).column = 2 lastcol cols.add cstr(i - 1), datawks.cells(1, i).value next datawbk.close false set datawks = nothing set datawbk = nothing output = datacapture(cols) end sub function datacapture(datacols object) on error goto errhandle dim conn object, rst object dim strconnection string dim classsql string, itemsql string, grpsql string, strsql string dim integer, fld object, d variant, lastrow integer set conn = createobject("adodb.connection") set rst = createobject("adodb.recordset") ' hard code database location , name ' ' strconnection = "driver={microsoft excel driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _ ' & "dbq=c:\path\to\data\workbook.xlsx;" strconnection = "provider=microsoft.ace.oledb.12.0;" _ & "data source='c:\path\to\data\workbook.xlsx;" _ & "extended properties=""excel 12.0 xml;hdr=yes imex=1;"";" ' open db connection ' conn.open strconnection each d in datacols.keys strsql = " select '" & datacols(d) & "' [column], [data$].[" & datacols(d) & "] items," _ & " sum(iif(year([item creation date]) >= year(date()) - 1, 1, 0)) new," _ & " " _ & " sum(iif(year([item creation date]) < year(date()) - 1, 1, 0)) old," _ & " " _ & " round(sum(iif(year([item creation date]) >= year(date()) - 1, 1, 0)) / " _ & " (select count(*) [data$] sub" _ & " year(sub.[item creation date]) >= year(date()) - 1),2) newpct," _ & " " _ & " round(sum(iif(year([item creation date]) < year(date()) - 1, 1, 0)) / " _ & " (select count(*) [data$] sub" _ & " year(sub.[item creation date]) < year(date()) - 1),2) oldpct" _ & " [data$]" _ & " group [data$].[" & datacols(d) & "]" ' open recordset ' rst.open strsql, conn ' column headers ' if d = 1 = 0 worksheets("results").range("a1").activate each fld in rst.fields activecell.offset(0, i) = fld.name = + 1 next fld end if ' data rows ' lastrow = worksheets("results").cells(worksheets("results").rows.count, "a").end(xlup).row worksheets("results").range("a" & lastrow + 1).copyfromrecordset rst rst.close next d conn.close msgbox "successfully processed sql query!", vbinformation exit function errhandle: msgbox err.number & " - " & err.description, vbcritical exit function end function output
Comments
Post a Comment