How to compress multiline data into single line?

[#1]

Hi,

Part of an application that I am working on is producing a table similar to below: -

Ref	1	2	3	4	5	6	7	8	9	10	11	12
A	3	0	0	0	0	0	0	0	0	0	0	0
B	7	0	0	0	0	0	0	0	0	0	0	0
A	0	2	0	0	0	0	0	0	0	0	0	0
B	0	6	0	0	0	0	0	0	0	0	0	0
B	0	0	0	0	0	0	0	0	0	8	0	0

I am trying to compress the data within the Load Script by Ref to achieve the following: -

Ref	1	2	3	4	5	6	7	8	9	10	11	12
A	3	2	0	0	0	0	0	0	0	0	0	0
B	7	6	0	0	0	0	0	0	0	8	0	0

I have tried varous 'if' statements and null() but none of it appears to work.  I would be grateful for any advice on this.

Many thanks

[SunilChauhan]

use trim of columnName in dimesion

[martin · Accepted solution]

Hi,

You have to do something like this in your load script :

AllTable:
CrossTable (RefNum, Content)
LOAD *
FROM MyTable;
Table:
LOAD Ref,
     RefNum,
     Sum(Content) as Sum
RESIDENT AllTable
GROUP BY Ref, RefNum;
DROP TABLE AllTable;

I'm not sure, I didn't try it. But I hope that helps you

Martin

[#4]

Martin,

Thankyou it works perfectly.  I did a generic load to twist it around again.

AllTable:
CROSSTABLE (RefNum, Content)
LOAD Ref,
     [1],
     [2],
     [3],
     [4],
     [5],
     [6],
     [7],
     [8],
     [9],
     [10],
     [11],
     [12]
FROM
[test.xls]
(biff, embedded labels, table is Sheet1$);

Table:
LOAD Ref,
     RefNum,
     Sum(Content) as Sum
RESIDENT AllTable
GROUP BY Ref, RefNum;

DROP TABLE AllTable;

Table2:
GENERIC LOAD
Ref as ref2,
     RefNum,
     Sum
    
RESIDENT Table;

DROP TABLE Table;