Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ericdelaqua
Creator
Creator

Add header and Crosstable

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.

range1232425
range226
range32931
range433

I need this output.

RangeHeaderN0value
range1header123
range1header224
range1header325
range2header126
range3header129
range3header331
range4header233

How can this be achieved in the script?

Thank You

3 Replies
petter
Partner - Champion III
Partner - Champion III

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;

2018-11-07 23_47_50-Window.png

ericdelaqua
Creator
Creator
Author

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
Partner - Champion III
Partner - Champion III

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