logo
Tags down

shadow

VBA: Creating a Pivot Table


By : Stephen Weaver
Date : October 18 2020, 01:08 AM
wish of those help I think you should check your Row 1 Columns A-N in SHeet 1 and make sure there is a unique, non-blank column name for each column in your source data. The first row contains the pivot table field names.
code :


Share : facebook icon twitter icon

Creating a pivot table in T-SQL?


By : Lana Xoshnaw
Date : March 29 2020, 07:55 AM
I wish this help you You should be able to join on the table multiple times to get the result that you need:
code :
select t1.region,
  t1.location,
  t1.customer,
  t1.customerkey,
  t1.status,
  case when t2.status is not null then 'Yes' else 'No' end VIPStatus
from yourtable t1
left join yourtable t2
  on t1.CustomerKey = t2.CustomerKey 
  and t2.Location = 'All States' 
  and t2.status = 'VIP Customer'
where t1.Location <> 'All States' 
| REGION |   LOCATION | CUSTOMER | CUSTOMERKEY |   STATUS | VIPSTATUS |
-----------------------------------------------------------------------
|  North |   New York |     John |         111 |   Active |        No |
|  North |   New York |     Mary |         112 |   Active |        No |
|  North |   Delaware |      Bob |         113 |     Idle |       Yes |
|  North | New Jersey |      Bob |         113 |   Active |       Yes |
|   West | California |      Bob |         113 | Inactive |       Yes |
|   West | Washington |     Greg |         114 | Inactive |       Yes |
|   West |       Utah |      Tim |         115 |   Active |        No |

creating a pivot table using sql


By : Vicente
Date : March 29 2020, 07:55 AM
I hope this helps . Since you want to convert your rows of data into columns, you need to PIVOT the data. This can be done a number of ways.
If you have a limited number of values that you are going to be returning, then you can use an aggregate function with a CASE expression:
code :
select 
    count(case when statusreason = 181 then 1 end) [181],
    count(case when statusreason = 20 then 1 end) [20],
    count(case when statusreason = 212 then 1 end) [212],
    count(case when statusreason = 232 then 1 end) [232]
from
(
    select inv.statusreason
    from invoicetbl inv (nolock)
    inner join trucktbl tru (nolock) 
        on inv.tru_key = tru.tru_key
    where inv.client_key = 123
      and inv.createdate > getdate() - 7
) d;
select [181], [20], [212], [232]
from
(
    select inv.statusreason
    from invoicetbl inv (nolock)
    inner join trucktbl tru (nolock) 
        on inv.tru_key = tru.tru_key
    where inv.client_key = 123
      and inv.createdate > getdate() - 7
) d
pivot
(
    count(statusreason)
    for statusreason in ([181], [20], [212], [232])
) p;
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(statusreasons ) 
                    from statusreasontbl
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' 
            from 
            (
                select inv.statusreason
                from invoicetbl inv (nolock)
                inner join trucktbl tru (nolock) 
                    on inv.tru_key = tru.tru_key
                where inv.client_key = 123
                  and inv.createdate > getdate() - 7
            ) x
            pivot 
            (
                count(statusreason)
                for statusreason in (' + @cols + ')
            ) p '

execute sp_executesql @query;

pandas pivot table, creating table by taking difference of multiple columns


By : Ganesh
Date : March 29 2020, 07:55 AM
I hope this helps . I have this pivot table that has a 2 level column filter. , A case of multi index slicing, sub() and concat.
code :
df = pd.DataFrame({('Y', 'Y4'): {('1/1/2015', 2L): 90, ('1/1/2015', 1L): 80}, ('X', 'X1'): {('1/1/2015', 2L): 20, ('1/1/2015', 1L): 10}, ('X', 'X2'): {('1/1/2015', 2L): 40, ('1/1/2015', 1L): 20}, ('X', 'X3'): {('1/1/2015', 2L): 60, ('1/1/2015', 1L): 30}, ('Y', 'Y3'): {('1/1/2015', 2L): 70, ('1/1/2015', 1L): 60}, ('Y', 'Y1'): {('1/1/2015', 2L): 10, ('1/1/2015', 1L): 20}, ('Y', 'Y2'): {('1/1/2015', 2L): 50, ('1/1/2015', 1L): 40}})
df.columns = pd.MultiIndex.from_tuples([('X','X1'), ('X','X2'), ('X','X3'),('Y','Y1'), ('Y','Y2'), ('Y','Y3'), ('Y', 'Y4')])
df.index.names = ['DATE','ID']
print df


              X           Y            
             X1  X2  X3  Y1  Y2  Y3  Y4
DATE     ID                            
1/1/2015 1   10  20  30  20  40  60  80
         2   20  40  60  10  50  70  90


idx = pd.IndexSlice
collection = []
for tup in filter(lambda x: x[0] == "Y", df.columns.tolist()):
    foo = -1 * df.loc[:,idx['X',:]].sub(df.loc[:,tup],axis=0)
    foo.columns = [str(tup[1]) + '-' + col for col in foo.columns.get_level_values(1)]
    collection.append(foo)

print pd.concat(collection,axis=1)


             Y1-X1  Y1-X2  Y1-X3  Y2-X1  Y2-X2  Y2-X3  Y3-X1  Y3-X2  Y3-X3  Y4-X1  Y4-X2  Y4-X3
DATE     ID                                                                                    
1/1/2015 1      10      0    -10     30     20     10     50     40     30     70     60     50
         2     -10    -30    -50     30     10    -10     50     30     10     70     50     30

Creating Pivot Table to new worksheet Error on table destination VBA


By : Madhur Patil
Date : March 29 2020, 07:55 AM
With these it helps this has been asked a lot of times but may I ask where is the error here , got it working with the help of @YowE3K. Please see his comment. :)
code :
Dim wsNew As Worksheet
    Set wsNew = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsNew.Name = "4038 future project"

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "'4038 Only'!R1C1:R211C87", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="'" & wsNew.Name & "'!R1C1", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion12

Empty Pivot Cache when creating a pivot table in VBA


By : user3009340
Date : March 29 2020, 07:55 AM
wish helps you The reason you're not getting an error is that you've included an On Error statement, which masks any errors. If you comment out or delete that line, you'll get an error here...
code :
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange). _
    CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
    TableName:="PipePivot")
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange)
Related Posts Related Posts :
  • INDEX MATCH result is always the same
  • Data Extraction over multiple worksheets
  • Running multiple subs one at a time
  • Copy a variably named worksheet from one workbook to another
  • Editing date to DD-mmm-YYYY format
  • Add x number of formated cells based on cell value
  • Excel - Looking up for next available data
  • Cucumber feature file in an excel spreadsheet
  • How to extract the characters from a string in Excel
  • Excel - splitting multiline cells into columns but keep relevant data grouped on same column
  • More efficient method in order to replicate function VBA/Excel
  • How to select multiple cells in VBA script
  • Excel VBA "Like" not Selecting for "*M*"
  • Find and replace the whole string when a partial match is achieved multiple sheets
  • Removing Rows - Code Too Slow
  • Lookup a date, based on a variable, and then add a number of days to that date, to give a new date
  • Error while creating Pivot Table automatically
  • Find sheet name of a worksheet using relative reference
  • Excel Formula to work out incremental figure defined by various monthly results
  • Best way to check for duplicates in VBA
  • Error 1004: Unable to Get CountIf Property
  • Selecting the correct starting point for an offset sum
  • Deleting rows when two blank rows are concurrent
  • VBA: Renaming the active sheet
  • How Hide columns according to a cell value
  • Copy worksheet to new workbook and change the name of the worksheet to a compose name
  • Dynamic Referencing in VBA Formula
  • Sort Dictionary Keys using ArrayList
  • Excel cell color formatting
  • Excel: Copying rows to table in another sheet using VBA
  • use macro enabled Excel file in OneDrive
  • Faster VBA to hide rows
  • Two Pivot Tables in the same excel sheet
  • For loop setting Font and Interior of Range taking way too long
  • shadow
    Privacy Policy - Terms - Contact Us © soohba.com