topic Re: Add header and Crosstable in QlikView

Add header and Crosstable

ericdelaqua — Wed, 07 Nov 2018 22:11:10 GMT

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

Re: Add header and Crosstable

petter — Wed, 07 Nov 2018 22:51:30 GMT

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;

Re: Add header and Crosstable

ericdelaqua — Wed, 07 Nov 2018 23:11:30 GMT

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

Re: Add header and Crosstable

petter — Thu, 08 Nov 2018 12:11:23 GMT

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...