Cross Table based on Column Name

[Sai33]

Hello All,

I'm looking to convert my Table based on Cross Table logic but the new Column Generated should be based on the Column Name of existing Columns

Sample Data:

Date	OrdersA	DeliveredA	OrdersB	DeliveredB
01.01.2020	10	5	15	10
02.01.2020	20	10	13	8

Expected Result:

Date	Orders	Delivered	Location
01.01.2020	10	5	A
01.01.2020	15	10	B
02.01.2020	20	10	A
02.01.2020	13	8	B

Thanks 

Sai

[Vegar · Accepted solution]

You could do something like this.

source:
load * INLINE [
Date, OrdersA, DeliveredA, OrdersB, DeliveredB
01.01.2020, 10, 5, 15, 10
02.01.2020, 20, 10, 13, 8
];

FOR each _location in 'A' , 'B'
  LOAD
    Date,
    Orders$(_location) as Orders,
    Delivered$(_location) as Delivered,
    '$(_location)' as Location
  Resident
    source;
next _location

Drop table source;