Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

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

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

Contributor III
Contributor III

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

MVP
MVP

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