topic Re: Transpose Columns into Rows from a Resident Table in App Development

Transpose Columns into Rows from a Resident Table

Alessandro_TZ9 — Thu, 19 Jan 2023 18:36:03 GMT

Hi to everyone,

I need to transpose only some columns into rows from a resident table,

from this:

Supplier	Value A	Percentage A	Value B	Percentage B
Ford	100	1	150	1.5
Brown	150	2.5	300	3
McCallan	180	1.8	270	2.4

.. into this, creating a new associated table:

Supplier	Value	Percentage
Ford	100	1
Ford	150	1.5
Brown	150	2.5
Brown	300	3
McCallan	180	1.8
McCallan	270	2.4

What is the simpliest way to proceed?

Many thanks in advance

Alessandro

Re: Transpose Columns into Rows from a Resident Table

SerhanKaraer — Thu, 19 Jan 2023 19:55:00 GMT

Hello Alessandro,

You can do it as below:

Load Supplier, Subfield(Fields, '|', 1) as Value, Subfield(Fields, '|', 2) as Percentage; LOAD Supplier, Subfield(Fields, ';') as Fields; LOAD Supplier, "Value A" & '|' & "Percentage A" & ';' & "Value B" & '|' & "Percentage B" as Fields; LOAD * INLINE [ Supplier Value A Percentage A Value B Percentage B Ford 100 1 150 1.5 Brown 150 2.5 300 3 McCallan 180 1.8 270 2.4 ] (delimiter is '\t'); exit script;

I hope it solves your issue.

Re: Transpose Columns into Rows from a Resident Table

MarcoWedel — Thu, 19 Jan 2023 20:39:08 GMT

tabResult: LOAD Supplier, [Value A] as Value, [Percentage A] as Percentage Resident YourResidentTable; Concatenate (tabResult) LOAD Supplier, [Value B] as Value, [Percentage B] as Percentage Resident YourResidentTable; Drop Table YourResidentTable;