I have an Issue. Please find the sample of my data source
StudentID
Fee 1
Fee 1 Currency Type
Fee2
Fee 2 Currency Type
1
10
USD
20
INR
2
45
EUR
20
USD
If I need to transform this data, it should be like below Table.
ID
StudentId
FeeType
FeeAmount
CurrencyType
1
1
Fee 1
10
USD
2
1
Fee 2
20
INR
3
2
Fee 1
45
EUR
4
2
Fee 2
20
USD
I have selected columns Fee 1 and Fee 2 in Unpivot Transformation. I set the "FeeAmount" as Destination Column Name and "FeeType" as Pivot Key Value Column Name. I left the Pivot Key Value as is for all the selected columns. So far no issue that I am able to get the Fee Type and Fee Amount columns for my destination table.
But the issue is how to get the Currency Type for each corresponding coulmns? I was not able to get the currency type for each student. I hope you got my point. Please let me know If have further queries and hel me out to find this.
My Data Flow works fine and everything is going without any errors, but my Destination is without the 3rd Attribute (CustomerPhone). Whats wrong in my configuration?
I am using the unpivot transformation, but I can't figure out how to use an expression in the Pivot Key Value.
The denormalized table I want to unpivot has columns like Sunday_Qty, Monday_Qty, Tuesday_Qty, etc. Just before the unpivot component, I inserted a derived column component that adds fields like DateSun, DateMon, DateTue, etc. that resolves to values like 01/07/2007, 01/08/2007, etc.
So for the various rows in the Unpivot Transformation Editor, I entered DateSun, DateMon, DateTue, etc. for the Pivot Key Value, and "EntryDate" for the pivot key value column name.
The data pipeline gets unpivoted correctly, but the rows have the literal values "DateSun", "DateMon", etc. in the EntryDate column.
How do I tell SSIS to use the DateSun column instead of the string "DateSun"?
Hi, I am trying to use the Unpivot Transformation, and I have the following mapped out, following the example from the SQL Server 2005 BOL Unpivot Transformation:
Col Name Subject Question ValueNumeric ValueString
Data Records 99999 RACE 1 NULL
99999 OTHRRACE NULL NULL
99999 GENDER 1 NULL
88888 RACE NULL NULL
88888 GENDER 2 NULL
88888 OTHRRACE NULL Hispanic
77777 RACE 2 NULL
77777 GENDER 2 NULL
77777 OTHRRACE NULL NULL
This doesn't work, however, as I have multiple data types. So unless someone knows a better way, I had to split it into two Unpivot Transformations, which I later Sort and Merge together. The two Unpivot Transformations look like this:
The first Unpivot works great, because the data is mandatory. However, the second is full of NULLs so the actual output from the second Unpivot, prior to the merge looks like this:
I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I try the SQL script below but it returning 6 rows instead of 3–
The following is a SAMPLE data from an excel spreadsheet. This SAMPLE data has many other fields as date. Here I have only used two date columns i.e. 28 Dec 2006 and 29 Dec 2006 This data needs to be exported into sql server 2005 table which has the fields below where I have placed the data into a table. How can this be done please?
data:
Ref Sector Name 28 Dec 2006 29 Dec 2006 1 Sovereign RUSSIA 05 null 173.21 2 Sovereign RUSSIA 07 102.99 102.22 3 Sovereign RUSSIA 10 114.33 104.63 4 Sovereign RUSSIA 18 115.50 145.50 ...
sql server table
create table tblData ( DataID int, Ref int, Sector varchar(20), Name varchar(20), Date datetime, value decimal(6,2) )
DataID Ref Sector Name Date value 1 1 Sovereign RUSSIA 05 28 Dec 2006 null 2 1 Sovereign RUSSIA 05 29 Dec 2006 173.21 3 2 Sovereign RUSSIA 07 28 Dec 2006 102.99 4 2 Sovereign RUSSIA 07 29 Dec 2006 102.22 5 3 Sovereign RUSSIA 10 28 Dec 2006 114.33 6 3 Sovereign RUSSIA 10 29 Dec 2006 104.63 7 4 Sovereign RUSSIA 18 28 Dec 2006 115.50 8 4 Sovereign RUSSIA 18 29 Dec 2006 145.50 ...
Hi Guys, wondered if anyone could point me in the right direction with this one.
I'm fairly new to SSIS and one data flow transformation i'm trying to get my head around is the pivot transformation. Perhaps if i explain what i want it to do it will make my problem seems clearer.
I have a table which contains a list of people and their demographic and personal details, each with a unique identifier number. When each persons details are entered one of the pieces of data captured is the month they were registered and their ethnic group classification. Within my data flow i want to take this table of registrations and pivot it like you would in Excel e.g. take the whole list, add months as the columns and ethnic categories as the rows and then a count of the unique identifiers as the value, giving you a simple count of all of the customers and what different ethnic groups per month were registered. Simple enough??
Now, i've tried to do this using the pivot and i seem to be getting lost so after reading the chapter from my wrox book i've tried to outline how i think it should be configured to see if it makes anymore sense, i'd appreciate it if anyone could point out where i might be going wrong.......
In my example i'm assuming my input columns will be the unique identifier, the ethnic group and the month?
I'll then select the ethnic group as the row and the month as the pivot and the unique code as the value using the pivotusage properties of 1, 2, 3. still on the right track??
I then create an output column of each month and set the pivotkey value to the month name and the lineageID to the ID of the unique identifer column.
Can anyone spot if i've missed something out here?
Does anyone have any ideas how to resolve this problem?
I have set my PivotKey to be a Column containing the ColumnNames which I want to Pivot. I have taken the LineAgeId value from the Input column and used it as my SourceColumnID in the Ouput Columns tab.
Error 1 Validation error. Data Flow Task: Pivot [231]: Output column "ATTRIBUTENAME" (489) cannot be mapped to PivotKey input column. Package.dtsx 0 0
I have a table with following structure: ProductID OrderType Value 1001 BaseSales 2000 1001 Incremental 1000 1001 TotalSales 3000 1002 BaseSales 2002 1002 Incremental 1003 1002 TotalSales 3005
I would like to get the data in following format: ProductID BaseSales Incremental TotalSales 1001 2000 1000 3000 1002 2002 1003 3005
I am using the pivot component in SSIS to pivot a set of data, and up until now it has worked fine. However lately it has started to produce duplicates, meaning 2 rows for a combination of the keys (type 1) used. It is completely random, meaning a combination of keys for one run can work correctly, and in the next run it inserts an extra record for the combination for just one of the pivoted values. This also means that for a batch of 10 mill rows input the output is completely random, because the number of duplicates vary.
Are there any memory restrictions or similar that causes this, or do you guys have any idea what might be wrong?
If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?
1) Production data with column headers: Key, Facility, Line, Time, Output 2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key Facility Line Time Output Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
Can I force the following measure to be visible for all rows in a pivot table?
Sales Special Visibility:=IF( Â Â HASONEVALUE(dimSalesCompanies[SalesCompany]) Â Â ;IF( Â Â Â Â VALUES(dimSalesCompanies[SalesCompany]) = "Sales" Â Â Â Â ;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany])) Â Â Â Â ;[Sales] Â Â ) Â Â ;BLANK() )
FYI, I also have other measures as well in the pivot table that I don't want to affect.
I have a simple pivot table (screenshot below) that has two variables on it: one for entry year and another for 6 month time intervals. I have very simple DAX functions that count rows to determine the population N (denominator), the number of records in the time intervals (numerator) and the simple percent of those two numbers.
The problem that I am having is that the function for the population N is not overriding the time interval on the pivot table when I use an ALL function to do so. I use ALL in other very simple pivot tables to do the same thing and it works fine.
The formula for all three are below, but the one that is the issue is the population N formula. Why ALL would not work, any other way to override the time period variable on the pivot table.
Population N (denominator): =CALCULATE(COUNTROWS(analyticJudConsist),ALL(analyticJudConsist[CurrentTimeInCare1])) Records in time interval (numerator): =COUNTROWS(analyticJudConsist) Percent: =[countrows]/[denominatorCare]
How can I apply "Min" formula under a "new measure" (calculated field) within a pivot table under Power pivot 2010?Can see that neither does it allow me to apply "min" formula directly "formula box" nor could find any other option.Intent formula: "=Min(1,sum(a:b))" this isn't allowed so all I can do is "=sum(a:b)".
I have simple pivot table (below screenshot with info redacted) that displays a population number ("N" below), this is the denominator, a cumulative numerator number (below "#") and a simple cumulative percent that just divides the numerator by the denominator. It cumulates from top to bottom. The numerator and percent are cumulative using the below functions. There are two problems with the numerator and percent:
1. When there is not a number for the numerator, there is no value displayed for both the numerator and the percent..There should be a zero displayed for both values. 2. When there has been a prior number for the numerator and percent (for a prior month interval) but there is no number for the numerator in the current month interval, the prior month number and percent are not displayed in the current month interval--see the 3rd yellow line, this should display "3" and "16.7%" from the second yellow line.Here is the formula for the numerator:
=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))) Here is the formula for the percent: =(CALCULATE(countrows(s1Perm1),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))
Could some body give me little help on this? The UNPIVOT is new to sql05, and I did not find many useful helps out there.
My understanding is that it will normalize the data in column name (the UNPIVOT FOR part). As my sample shows here, I want to get that from one of the row.
Here is the sample, and of cause the Select PIVOT part is not working.
Also, if UNPIVOT is not the right way to go after it, what is the right way?
Thanks!
USE tempdb; GO If Object_ID('#pvtSales') IS NOT NULL Drop table dbo.#pvtSales; GO
I have a table with the following fields: ID, StartDate, Day1, Day2, Day3. This table is filled with hours.
I want to unpivot this table to get the following: ID, Date (=Start date), hours ID, Date + 1 (=Start date + 1), hours ID, Date + 2 (=Start date + 2), hours
The trouble I have concerns the dates. Please help.
I know I could achieve this by using SQL and UNION ALL: SELECT Col1, Code1 as Code, Amt1 as Amt, Col<n>... FROM <mySource> UNION ALL SELECT Col1, Code2 as Code, Amt2 as Amt, Col<n>... FROM <mySource>
But I was wondering if the built-in unpivot transform could do the job here.
I have data in my Powerpivot window which was generated by a sql query. This data includes a field named 'Cost' and every row shows a value for 'Cost' greater than zero. The problem is that when I display this data in the pivot table all entries for Cost display as $0. At first I thought that maybe Cost was set to a bogus data type (such as 'text) but it is set to ''Decimal Number' so that's not the problem.Â
What is happening and how do I fix it so that my pivot table reflects the values for 'Cost'?
I have a data table that contains budget and actual data by month. Â I use the data to create a pivot that shows actual results next to budgeted results. Â I need a column that shows that variance between those columns. Â I think my issue is that the "Type" field contains actual and Budget. Â I sum on "Type". Â I can't seem to create a sum since those items are in the same field or am I missing something?
How to get a list of values to actually display in correct order in either a slicer or when on an axis on a pivot table?
I currently have the below list and have tried to add a preceding numeric (ex. "1. <=0") or preceding blank space, neither of which is visually great. Is there another way?
I am using excel 2010 and creating pivot table from Power Pivot. I created a pivot table with department slicers. All is good, the problem I am having is whilst in an unfiltered position (ALL) of the slicers (departments) I get 200 pages, now when I click on a given department with say 10 pages, I still get the same 200 pages with the first 10 pages showing the data from the clicked department and 190 blank pages.
All I want is to get a WYSIWYG (What you see is what you get) of what is on the screen as my print, but I am getting extra blank pages right after the data. Â How do I resolve this.
Below are the steps I go thru to printÂ
1. Select slicers in unfiltered position (ALL) 2. Select entire pivot table 3. Select Page layout and select print area. 4. Save 5. Click on Print Preview to preview the print 6. Click on a given department in the slicer and repeat item 5, but this gives me blank pages after the data.
