topic Re: How to compress multiline data into single line? in QlikView

How to compress multiline data into single line?

Wed, 03 Aug 2011 14:43:53 GMT

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

How to compress multiline data into single line?

SunilChauhan — Wed, 03 Aug 2011 14:48:35 GMT

use trim of columnName in dimesion

Re: How to compress multiline data into single line?

martin — Wed, 03 Aug 2011 14:49:26 GMT

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

Re: How to compress multiline data into single line?

Wed, 03 Aug 2011 15:32:09 GMT

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;

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

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

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