topic Re: Combine data in multiple columns in QlikView

Combine data in multiple columns

Tue, 07 Jul 2015 02:03:51 GMT

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

Re: Combine data in multiple columns

sunny_talwar — Tue, 07 Jul 2015 02:08:55 GMT

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

Re: Combine data in multiple columns

jagan — Tue, 07 Jul 2015 03:07:51 GMT

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.