excel-2007,transform,ms-query , Transform and Pivot in Excel - MS Query

Transform and Pivot in Excel - MS Query


Tag: excel-2007,transform,ms-query

I have 4 field in my table :

NO. | Specification | Specification Value | Description

1      Color               Blue             a
1      Size                 6"              a
2      Color               Red              b
2      Size                 8"              b

I am trying to Pivot it and its giving me an error.

TRANSFORM FIRST(Specification Value)
SELECT items.'No.'
FROM items
GROUP BY items.'No.'
PIVOT Specification

The error is : "Syntax error(missing operator) in query expression 'FIRST(Specification Value)'."

Please help. Thank you


Remove the space in the Specification Value. The First() function takes one expression or argument. You are passing it two, so it thinks you are passing an expression with two arguments and have forgotten the operator.


Cannot Retrieve Data from Excel File Created using Aspose.Cells

I create an Excel file (.xlsx) using the Aspose.Cells library. But I'm not able to read the data (retrieve rows) using OleDb commands after that, until I open the file and save it manually. I'm running something as simple as this one: new OleDbDataAdapter("select * from [Sheet1$]", conn); // etc......

XslCompiledTransform Transform Method Not Working

I have a below xslt file. <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:gl-bus="http://www.xbrl.org/int/gl/bus/2006-10-25" xmlns:gl-cor="http://www.xbrl.org/int/gl/cor/2006-10-25" xmlns:gl-gen="http://www.xbrl.org/int/gl/gen/2006-10-25" xmlns:iso4217="http://www.xbrl.org/2003/iso4217" xmlns:link="http://www.xbrl.org/2003/linkbase"...

De-Stacking columns in Excel using VBA

I have a dataset in three columns composed of a repeating set of UUIDs in the first column, string responses for each UUID in the second, and a code for each response in the third. I need to break this out into multiple sets of columns, one for each repeating...

Shuffle values to a new row two cells at a time

To explain it in the easiest way possible: | 1 | 2 | 3 | 4 | | 5 | 6 | 7 | 8 | ... needs to look like: | 1 | 2 | | 3 | 4 | | 5 | 6 | | 7 | 8...

Javascript code for CSS3 animation and transform property

I know, with CSS, we have to use vendor prefixes such as -ms-, -moz-, -webkit- and -o- for transform property. And for animation property just -moz-, -webkit- and -o-. I want to know how to set each of them from Javascript? And transition property also. Looking for something like this...

VBA - test if a value is a valid selection for a PivotField

For a pivot table (pt1) on Sheet1, I use VBA to change the value of a filter field (filterfield) using the code below. Let's say values for field can be A, B or C Sheets("Sheet1").PivotTables("pt1").PivotFields("filterfield").CurrentPage = "A" Occasionally, and let's say randomonly for the purposes of this question, A, B...

How does using CSS 3D Transforms affect SEO? [closed]

Google's made a big deal of "if you hide it, it won't count." However, google cache shows it doesn't understand a lot of things correctly. Maybe google sees something as hidden when it should show or shows when it should be hidden. So, I ask about CSS 3D transforms. Take...

Convert String “2008-02-10T12:29:33.000” to a Date with the same format 2008-02-10T12:29:33.000 in Java [duplicate]

This question already has an answer here: Java string to date conversion 9 answers I have an ArrayList that contains strings like 2008-02-10T12:29:33.000 [2008-02-11T12:29:33.000, 2008-02-10T12:29:33.000]... I want to sort this ArrayList in natural order. For this I have to convert this strings into a Date I quess. The order...

R loading multiple excel files and merge

I have many excel files that I need to load and merge into a single data frame. The script below works! However, before merging all files I want to paste each file name in a new column. library(gdata) library(tools) filelist <- list.files(pattern = "*\\.xlsx$") files = lapply(filelist, read.xls, header=TRUE) new...

Replacing the existing sheet in excel 2007 using VBA

Sub testmac() ' ' testmac Macro ' ' ActiveWorkbook.Names.Add Name:="items", RefersToR1C1:= _ "=Report!R7C8:R486C11" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _ "ODBC;DSN=Excel Files;DBQ=path ), Array("P;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;")), Destination:= _ Range("$A$1")).QueryTable .CommandText = Array( _ "TRANSFORM first(`Dimension Value Code`)" & Chr(13) & "" & Chr(10) & "SELECT items.`No#`, items.`Description`" & Chr(13) & "" & Chr(10) &...

Missing Microsoft Excel as option in SQL Server data import tool

I'm running SQL Server 2012 on a 64bit Windows Server 2012 R2. I don't have MS Office installed. I now notice that when I start Start->Program Files->Microsoft SQL Server 2012->Import and Export Data (64-bit) the MS Excel file option is missing from the datasource dropdown. How can I get that...

Object required error '424' when iterating over Collection

When I run my function, I get the following error: Run-Time error '424': Object Required And it highlights the following line of code: For Each n In DataSet Here is my full code: '==============================|Find_Occurrences FUNCTION|==============================' ' Given a collection and a search value, return the number of occurrences of the...

Transform and Pivot in Excel - MS Query

I have 4 field in my table : NO. | Specification | Specification Value | Description 1 Color Blue a 1 Size 6" a 2 Color Red b 2 Size 8" b I am trying to Pivot it and its giving me an error. TRANSFORM FIRST(Specification Value) SELECT items.'No.' FROM...

Strange number-of-character-in-a-cell limitation when programmaticaly (VBA, C#) set array string to a range

I have run into an issue in Excel 2007 which is very similar to a known problem of Excel 2003 described by Microsoft Support here ("You may receive a "Run-time error 1004" error message when you programmatically set a large array string to a range in Excel 2003"). The problem...

CSS Transform scale scrolling issue

I am creating a web app that uses CSS' transform scale property. As shown by the image below, I have an object inside of a container, which fits nice and snugly inside, without any overflowing content. This is how I wish for it to be. My issue is brought up...

Translate object, stopping, and continuing- Processing

I'm new to Processing, so please bare with me. I'm trying to move an object (rectangle for our purposes), from point A to D. However, along the way I want to stop the motion at different segments and wait some time. For example, I want to move from A to...

Unity3D: Moving a child to orbit a parent

I have made a game where I make two spheres, one child sphere and one parent sphere. The parent sphere is bigger than the child sphere, and I am trying to make the child move around the parent sphere. In the following code, the child sphere moves in a circle....

Unity3D: Adding force based on direction an object is facing

I am trying to make a "worms"-like game where the player can choose the position of an object (the player can move the object around 180 degrees) , and then the force would be added to the direction the object is facing. I tried using transform.right and transform.forward, but the...

solvePnP: Obtaining the rotation translation matrix

I am trying to image coordinates to 3D coordinates. Using the solvePnP function (in C++)has given me 3X1 rotation matrix and 3X1 translation matrix. But isn't the [R|t] matrix supposed to be 3X4? Any help will be greatly appreciated!...

Rotate Text in Bootstrap in different col sizes

I'm having a lot of different col sizes, so when I place my follow code in there, the rotated text is different in every col. CSS: .verticaltext { position: relative; } .verticaltext_content { -webkit-transform: rotate(-90deg); -moz-transform: rotate(-90deg); -ms-transform: rotate(-90deg); -o-transform: rotate(-90deg); filter: progid:DXImageTransform.Microsoft.BasicImage(rotation=3); left: -60px; top: 35px; position: absolute; color:...

Excel lookup by text

I am trying to make lookup for excel table using the text in the columns For example if i have A1 | A2 BMW-X6 | 1 BMW-X5 | 1 Mazda-1 | 2 Mazda-2 | 2 Mazda-3 | 2 Mercedes-Benz | 3 Mercedes- cls | 3 I want where there is...

Always Return the Previous Saturday's Date Using NOW Function in MS Excel 2007

I have a workbook that I am using the NOW function to return today's date and then I have another cell to convert to WEEKNUM that I use to MATCH on with a different tab. I need to be able to create a formula to read today's date and always...

Create a CSS3 flip-down animation for menus

I know there are plenty of examples out there but I'm having trouble making this work with my specific HTML. The HTML cannot be changed, it's Wordpress generated. I have a menu with a sub-menu containing two items. I want to create a CSS3 animation that flips down the children...

Need to keep a CSS transform on page load for “active” nav link

I have a navbar that uses CSS transform to extend a bar across the top of the link on hover. I would also like to have an #active id, so the corresponding link has the ending style applied to it. Due to the nature of CSS transform syntax, I cannot...

transform-origin doesnt apply in safari

Why doesn't transform-origin doesn't work in Safari? Here, how it should look (Chrome): http://i.imgur.com/f3zBu8e.png Here, how it looks in Safari: http://i.imgur.com/0XrPYXs.png I already tried some stuff with percents but it my content divs are having different sizes, so it looks awkward. Here's the JSFiddle. http://jsfiddle.net/2f4pferq/ text-align: right; -ms-transform: rotate(-90deg); -ms-transform-origin:...

Multiply values based on numerous criteria

I've been having a lot of trouble lately with this and haven't been able to find a solution yet. Here's my problem: I have two sheets: http://s4.postimg.org/aryzvl13v/Capture.png < image of the two tables I have to find the "sell price" in the first table. In order to do this I...

Search columns and return number of cells containing “5” against colleague name

What I'm looking for is a formula that will do the following: (I'll be using the first row for this example): Firstly check cells E:J for cells containing a 5. If all cells contain a 5 it will check the person's name and then add this to the box in...

Perl specific transforms in python

I need a Perl-specific code in Python. Here is the Perl code: use strict; use warnings; my $s = 'http://www.bergdorfgoodman.com/Ippolita-18k-Gold-Rock-Candy-Mini-Single-Square-Pendant-Necklace/prod108010011/p.prod#.U75MVqY-PtS'; $s =~ s/(.*\.com)\/[^\/]+(\/prod[^\_]*\/p\.prod).*/$1$2/si; print $s ."\n"; I have handled the capturing part by $1 and $2, and I don't know how to do it in Python. Output: http://www.bergdorfgoodman.com/prod108010011/p.prod ...

Changing the active cell

I was looking to create a program that examined a column of text in excel and extracted the first line that contained currency. The currency was in Canadian dollars and is formatted in "C $##.##" with no known upper bound but unlikely to reach 10,000 dollars. I was hoping to...

Apply transformation to result of previous transformation on SVG path

If I apply a translation of (100, 0) to an SVG path, it shifts left by 100px. But if I first rotate the path, then the translation happens along a diagonal. I understand why this happens -- the rotation has rotated the axes, thus making the translation along the x...

OpenGL - translation stretches and distorts sprite

I am loosely following the very handy tutorial at opengl-tutorial.org. I've been able to create a mesh, draw a sprite to it, and rotate and scale that mesh perfectly fine. However, I'm running into some issues when trying to translate the mesh. (pictures below) Here's an update function on the...

Rotate a basis to align to vector

I have a matrix M of size NxP. Every P columns are orthogonal (M is a basis). I also have a vector V of size N. My objective is to transform the first vector of M into V and to update the others in order to conservate their orthogonality. I...

XSLT Transforming XML into a cross referenced, nested HTML lists when source nodes are siblings and nesting is based on attribute values

Problem: I need to create a nested HTML unordered list from XML this is not nested. Additionally I need to cross reference the XML with an 'allowed nodes' section that is also contained in the document. Example XML: <content> <data> <navigation> <link name="about us" url="#"/> <link name="staff" url="staff.asp" parent="about us"/>...

ROS - Getting nan values with navsat_transform_node from robot_localization package

I need to fuse gps, imu and odometry data, so I started to test robot_localization package. I’m publishing valid mock messages sensor_msgs/Imu, and nav_msgs/Odometry for the inputs of ekf_localization_node, then I’m feeding the input of navsat_transform_node with the odometry message from the output of ekf_localization_node and a mock sensor_msgs/NavSatFix message....

is there a shortcut to open filter>contains box in excel?

I use filters a lot. Filter > contains option But it is slow because everytime i need to do several clicks. So, my question is, is there a shortcut to open filter, drop down, contains window?...

XSLT: Get all children of parents with specified attributes

Given this XML: <?xml version="1.0" encoding="iso-8859-2" ?> <r> <products start="5" stop="8"> <p> <id> 50 </id> <name> Murphy </name> <price> 33 </price> </p> <p> <id> 10 </id> <name> Margarethe </name> <price> 11 </price> </p> </products> <products start="1" stop="4"> <p> <id> 555 </id> <name> XXXX</name> <price> 333 </price> </p> <p> <id> 10...

Unity3D: Adding charged force in relation to position

I am trying to make a game which "kind of" simulates the shooting of the "worms" game. The player can choose the position (circular) of an object and then, the force that is applied to the object should move in the direction its pointing towards. I tried using the AddForce(transform.right)...

Apply scale and rotation to a specific point - Javascript

We've been having some trouble with scaling AND rotating a point in relation to another div. Essentially we have 2 layers, one with and image(svg) and another layer with icons that are placed on the image. We've been able to rotate the icons based on the image's rotation (and position),...

3d transforms on SVG

Is it possible to achieve 3d like perspective transforms on an SVG element ? I'm talking about something similar with how the Star Wars opening titles look like. This is a jsfiddle I made with the same desirable effect achieved using css3 transform: <section style="transform: perspective(200px) rotateX(-30deg); transform-origin: 50% 100%;...

Character is moving in weird ways when using Transform.forward in Unity

I've looked all around Google and have yet to find something to fix this problem. I am using this extremely simple code in a 3D game I am creating in the Unity Game Engine 5.0. It isn't working out too well, it is hard to explain the thing that happens,...

Calculate new position post rotation

I'm having trouble centering a div post rotation. Here's my fiddle: https://jsfiddle.net/cfy2ztdz/ Move between slides with keyboard 1 & 2 You can see that it finds the middle perfectly without rotation, but post rotation, moving back to 1, moves off page - instead of back to the middle. Where's my...

Transform rhombus defined by corners to another rhombus

If you have a 2D model in the shape of a rhombus, defined by it's four corners, and you want to transform it into the shape of another rhombus, given it's 4 corners, how would you do this? Can you do it with a transformation matrix? My line of thinking...

Css Transform - Trigger event each time the mouse is out and in of a div

i have two questions. In my project that i will include the code in this post i want: The transform should trigger when i hover on blue square. The square should not return to the first position if the mouse is in the red rectangle area or red square, only...

Calculating scale and position for stretching an object sideways

I'm trying to implement object stretching through the use of a selection bounding box, in 2D orthographic views of 3D space. The type of effect I'm trying to get is like when an object is surrounded with a selection marquee, and the sides and corners can be pulled to change...

CSS :hover transform scale command rotate object instead

.arrow { display: inline-block; height: 8px; width: 8px; margin-top: 20px; border-top: solid black 2.5px; border-left: solid black 2.5px; -ms-transform: rotate(-45deg); -webkit-transform: rotate(-45deg); transform: rotate(-45deg); -webkit-transition: transform 1s; -moz-transition: transform 1s; transition: transform 1s; cursor: pointer; } .arrow::before { content: ""; display: inline-block; width: 18px; margin-left: -3px; margin-bottom: 7px; border-top:...

How to rotate image to its next degree on button click event?

I am playing with jquery.rotate.js and I want to rotate image everytime to next rotation degree. I have included following jquery librarys: <script type='text/javascript' src='http://code.jquery.com/jquery-1.7.1.js'></script> <script type='text/javascript' src='jquery.rotate.1-1.js'></script> jQuery Code: $(window).load(function() { $("body").on("click", "#button", function() { $(".north").rotate(a()); }); nextAngle = 0; function a() { nextAngle += 90; if (nextAngle >=...

Extrude effect with scaleZ

Is it possible to use scaleZ() to effectively create a 3D box? Here's what I've tried, but obviously it didn't scaleZ at all: .box { -webkit-transform-style: preserve-3d; -moz-transform-style: preserve-3d; -ms-transform-style: preserve-3d; -o-transform-style: preserve-3d; transform-style: preserve-3d; -webkit-transform: scaleZ(10); -moz-transform: scaleZ(10); -ms-transform: scaleZ(10); -o-transform: scaleZ(10); transform: scaleZ(10); width: 100px; height: 100px; display:...

Returning just the date portion of =NOW()-(WEEKDAY(NOW(),1)) in Excel 2007

I need to return just the date portion of the following formula: =NOW()-(WEEKDAY(NOW(),1)). Any ideas? I am using this formula to get the current date and then return the previous Saturday's date, but I am also getting the time component and I need to drop that portion.

ssis swap some values in a data flow if they match a lookup table

Here's my problem - Midstream in my data flow, we have some values in one column that we want to swap for other values based on a lookup table. For example, if I had a rowset like this: Key Value 1 A 2 B 3 A 4 C 5 D...