Add header and Crosstable

[ericdelaqua]

Hello,

I need help in the script with the attached scenario. my excel table doesn't have header and I would like to crosstable as well.

first I need to assign the headers while reading the data then crosstable. Note that the first column is always the main to keep ignoring any blank rows. The big issue is that sometimes they may be 1 value column 2 or even 5. I need to identify how many value column are available and name them headers 1,2,3 etcc.

my excel data looks like this.

range1	23	24	25
range2	26
range3	29		31
range4		33

I need this output.

Range	HeaderN0	value
range1	header1	23
range1	header2	24
range1	header3	25
range2	header1	26
range3	header1	29
range3	header3	31
range4	header2	33

How can this be achieved in the script?

Thank You

[petter]

DATA0: 
CROSSTABLE (HeaderN0,value)
LOAD  
  @1 AS Range,
  @2 AS header1,
  @3 AS header2,
  @4 AS header3
INLINE [
range1,23,24,25
range2,26 
range3,29,,31
range4,,33
] (no labels);

DATA:
NOCONCATENATE LOAD * RESIDENT DATA0 WHERE value <> '';

DROP TABLE DATA0;

[ericdelaqua]

Hi Peter,

this is close but I don't always know how many headers will be in the excel file it can be 1 ,2 or even 5 header so I need a away to not hard code that bit.

Thanks

[petter]

You can use:

LOAD

   *

Instead of

LOAD

    @1 AS Range,

    ....

Then afterwards you can rename the fieldnames by iterating through the columnames/fieldnames that can be found by FieldName() function and use the NoOfFields() function to know the exact number of columns/fields...