Combine data in multiple columns

[#1]

Hi

I have a table with the following Headers:

Customer Name     Customer ID     Date     Purchase1     Price1     Purchase2     Price2     Purchase3     Price3...Purchase10     Price10

Is there a way to combine the Purchase and Price columns such that I only see:

Customer Name     Customer ID     Date     Purchase     Price

This will enable me to click on a customer's name and view all purchases as a list, instead of having to view it as Purchase1/2/3/4/5...

I've tried to rename the headers to the same name, but I got an error message that says headers need to be unique

[sunny_talwar · Accepted solution]

Do like this may be:

Table:

LOAD [Customer Name],

          [Customer ID],

          Date,

          Purchase1 as Purchase,

          Price1 as Price

FROM xyz;

Concatenate(Table)

LOAD [Customer Name],

          [Customer ID],

          Date,

          Purchase2 as Purchase,

          Price2 as Price

FROM xyz;

Concatenate(Table)

LOAD [Customer Name],

          [Customer ID],

          Date,

          Purchase3 as Purchase,

          Price3 as Price

FROM xyz;

HTH

Best,

Sunny

[jagan]

Hi,

If you have fixed Number of purchases then use Concatenate() like below

Data:

LOAD

[Customer Name],

[Customer ID],

Date,

Purchase1 AS Purchase,

Price1 AS Price

FROM DataSource;

Data:

LOAD

[Customer Name],

[Customer ID],

Date,

Purchase2 AS Purchase,

Price2 AS Price

FROM DataSource;

;

;

;

;

;

;

;

Data:

LOAD

[Customer Name],

[Customer ID],

Date,

Purchase10 AS Purchase,

Price10 AS Price

FROM DataSource;

If Purchases are dynamic then try this script

Temp:

CrossTable(Invoice, Value)

LOAD * INLINE [ 

    Customer, Inv1, val1, Inv2, val2

    1, 10, 100, 20, 50

    2, 11, 200, 21, 100

    3, 12, 300, 22, 20

    4, 13, 100, 23, 10

];

Data:

LOAD

Customer,

InvoiceNumber,

InvoiceValue

WHERE Len(Trim(InvoiceValue)) > 0;

LOAD

Customer,

If(Invoice Like 'val*', Previous(Value)) AS InvoiceNumber,

If(Invoice Like 'val*', Value) AS InvoiceValue

RESIDENT Temp;

DROP TABLE Temp;

Regards,

Jagan.