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


Transform and Pivot in Excel - MS Query

Question:

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


Answer:

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.


Related:


Apply transformation to result of previous transformation on SVG path


svg,transform,snap.svg
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...

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


c#,unity3d,transform
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,...

Cannot Retrieve Data from Excel File Created using Aspose.Cells


excel,excel-2007,aspose,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......

ROS - Getting nan values with navsat_transform_node from robot_localization package


localization,transform,nan,ros
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....

Transform and Pivot in Excel - MS Query


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...

Replacing the existing sheet in excel 2007 using VBA


excel,vba,excel-vba,excel-2007
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) &...

CSS :hover transform scale command rotate object instead


css,hover,transform,scale
.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:...

R loading multiple excel files and merge


r,loops,merge,excel-2007,lapply
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...

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


excel-vba,excel-2007,pivot-table
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...

XSLT: Get all children of parents with specified attributes


xslt,transform
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...

solvePnP: Obtaining the rotation translation matrix


c++,opencv,matrix,computer-vision,transform
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!...

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


excel,excel-formula,excel-2007,formula
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...

Shuffle values to a new row two cells at a time


excel,excel-vba,excel-formula,excel-2007
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...

XslCompiledTransform Transform Method Not Working


c#,xml,xslt,transform
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"...

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


java,string,data,transform,simpledateformat
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...

Changing the active cell


excel,vba,excel-vba,excel-2007
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...

Unity3D: Moving a child to orbit a parent


c#,unity3d,position,transform
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....

OpenGL - translation stretches and distorts sprite


c++,opengl,transform,translation,distortion
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...

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


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.

Extrude effect with scaleZ


css,css3,transform,css-shapes
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:...

Calculating scale and position for stretching an object sideways


c++,resize,geometry,transform,scaling
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...

Missing Microsoft Excel as option in SQL Server data import tool


sql-server-2012,64bit,excel-2007,import-from-excel,windows-server-2012-r2
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...

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


html,xml,xslt,transform,xslt-1.0
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"/>...

De-Stacking columns in Excel using VBA


excel,vba,excel-vba,excel-2010,excel-2007
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...

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


excel,excel-formula,excel-2007
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?...

Apply scale and rotation to a specific point - Javascript


javascript,math,rotation,transform,scale
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),...

Rotate a basis to align to vector


matlab,matrix,rotation,transform
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...

Perl specific transforms in python


python,regex,perl,transform,canonicalization
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 ...

Multiply values based on numerous criteria


excel,if-statement,excel-formula,excel-2007
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...

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


excel,vba,excel-vba,excel-2007,excel-addins
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...

3d transforms on SVG


css,css3,svg,transform
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%;...

Calculate new position post rotation


jquery,html,math,transform,velocity
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


graphics,transform
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...

Javascript code for CSS3 animation and transform property


javascript,css3,animation,transform,vendor-prefix
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...

Unity3D: Adding charged force in relation to position


c#,unity3d,position,transform
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)...

Rotate Text in Bootstrap in different col sizes


css,twitter-bootstrap,transform
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:...

Unity3D: Adding force based on direction an object is facing


c#,unity3d,transform
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...

Excel lookup by text


excel,excel-2010,excel-2007
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...

transform-origin doesnt apply in safari


css,safari,transform
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:...

Create a CSS3 flip-down animation for menus


css,css3,transform
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...

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


html,css,transform
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...

Translate object, stopping, and continuing- Processing


java,object,for-loop,transform,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...

CSS Transform scale scrolling issue


html,css,css3,transform
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...

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


ssis,sql-server-2008-r2,transform,lookup
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...

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


jquery,html,css,rotation,transform
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 >=...

Object required error '424' when iterating over Collection


excel,vba,excel-vba,excel-2010,excel-2007
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...

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


css,3d,seo,transform,googlebot
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...

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


html,css,css3,animation,transform
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...

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


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...