Forum Discussion
Unpivot Multiple Columns - Desktop
Hi bigdee008
Please check all the applied steps in Query Editor.The last step fill down can be used for other columns you need.
Regards,
Cherie
Thanks for the quick Reply Greg.
The file in its current state has 65 Columns.
In a desired future state, its should have only about 27 Columns.
I transposed the the data in my post to make it more meaningful because the table got stripped out while it got posted.
The columes are hardly reconizable
| Current Headers | AD_TYPE | AD_WEEK | AD_YEAR | PROGRAM_TYPE | PROMO_START_DT | PROMO_END_DT | Vendor | SP_RETAIL_PRICE | U_PUR_ORG_NUM | U_PUR_ORG_NM | U_DIST_CHANNEL_NUM | U_DIST_CHANNEL_NM | B00_QTY | B01_QTY | B01_TRANSMIT_DT | B01_DELIVERY_DT | B01_PICK_DT | B01_PREQ_CREATE_DT | B01_SOURCE | B01_LDGGROUP_VSR | B02_QTY | B02_TRANSMIT_DT | B02_DELIVERY_DT | B02_PICK_DT | B02_PREQ_CREATE_DT | B03_QTY | B03_TRANSMIT_DT | B03_DELIVERY_DT | B03_PICK_DT | B03_PREQ_CREATE_DT | B04_QTY | B04_TRANSMIT_DT | B04_DELIVERY_DT | B04_PICK_DT | B04_PREQ_CREATE_DT | B05_QTY | B05_TRANSMIT_DT | B05_DELIVERY_DT | B05_PICK_DT | B05_PREQ_CREATE_DT | B06_QTY | B06_TRANSMIT_DT | B06_DELIVERY_DT | B06_PICK_DT | B06_PREQ_CREATE_DT | B07_QTY | B07_TRANSMIT_DT | B07_DELIVERY_DT | B07_PICK_DT | B07_PREQ_CREATE_DT | B08_QTY | B08_TRANSMIT_DT | B08_DELIVERY_DT | B08_PICK_DT | B08_PREQ_CREATE_DT |
| Desired New Header | AD_TYPE | AD_WEEK | AD_YEAR | PROGRAM_TYPE | PROMO_START_DT | PROMO_END_DT | Vendor | SP_RETAIL_PRICE | U_PUR_ORG_NUM | U_PUR_ORG_NM | U_DIST_CHANNEL_NUM | U_DIST_CHANNEL_NM | QTY | TRANSMIT_DT | DELIVERY_DT | PICK_DT | PREQ_CREATE_DT | SOURCE | LDGGROUP_VSR |
OK, well then this really comes down to how you want to combine the columns that you want to combine. So, for example, let's say you have columns A, B and C and you want a single column for them. What are the "rules"?
Because let's say you just want the sum of those in a column, you could create a new column:
D := [A] + [B] + [C]
Then you would delete columns A, B and C in the next step.
Thanks Greg.
So my situation is, simply put;
currently, i have Colums A ,B, C, D1, D2, D3, E1, E2, E3
I want to make the colums A, B, C, D, E. -- .i.e. all transaction data under D1 to D3 will go under D likewise for E1-E3 will go under E without losing their mapping.
Im so sorry If im not being clear enough :smileysad:
Nope, not clear yet, let's try this:
Source data looks like:
A,B,C,D1,D2,D3,E1,E2,E3
bob,b1,c1,d1,d12,d13,e1,e12,e13
suzy,b2,c2,d2,d22,d23,e2,e22,e23
And I want it to end up like:
A,B,C,D,E
bob,b1,c1,d1,e1
bob,b1,c1,d12,e12
bob,b1,c1,d13,e13
suzy,b2,c2,d2,e2
suzy,b2,c2,d22,e22
suzy,b2,c2,d23,e23
Is that correct? If not, provide an example, with data for your source and your output, it's the only way to be clear.
bigdee008 - can you provide actual data example like above?
Hi Greg_Deckler Please see my screenshot below
I went ahead and took a stab at this. Here's the final output of what I did:
Not entirely sure if that is what you are looking for ( or maybe more of a step in the correct direction). If I understand you correctly, the next step would be renaming the Current Head with the New Header?
Thanks a lot Nick and Greg, below screengrab shows the row 1 or my actual data set. Maybe that will shed more light on what im trying to accomplish.
I came across the EXACT same thing im trying to accomplish here in the link, however, im not sure how to modify the query utilized there: