Excel transform using CrossTable

[Energetic]

I'd like to change this excel table using crosstable.

How can I use CrossTable script?

A,B,C are Categories

aa,bb,cc,dd,ee,ff are Subjects

Numbers are Scores

[Quy_Nguyen]

Hi,

As my understanding, direct load for this case is not supported in QlikSense. But we can solve it with a trick:

Consider aa,bb as header level 2

                A, B as header level 1

1. Load table from Name, header level 2, use cross table function as normal.

2. Load a map for mapping header level 2 with level 1 like aa, bb to A, B...

3. Apply map to get header level 1

An example:

// Cross load from Name row
CrossTable:
CrossTable(Header2, Value, 1)
LOAD * INLINE [
NAME, aa, bb, cc, dd, ee, ff
AAA, 11,12,13,14,15,16
BBB, 21,22,23,24,25,26
CCC, 31,32,33,34,35,36
DDD, 41,42,43,44,45,46
];

// Create map header
MapHeader:
Mapping LOAD * INLINE [
Header2, Header1
aa, A
bb, A
cc, B
dd, B
ee, C
ff, C
];

// Map data
FinalData:
Load 
 NAME,
 ApplyMap('MapHeader',Header2, 'Unknown') As Header1,
    Header2,
    Value
Resident CrossTable;
Drop Table CrossTable;

Result on Qlik:

[Energetic]

you mean, I can't load this excel table thru qlik sense, right?

if so, how about eliminate Header level 1 then load it thru qlik sense.

after that, using inline script and crosstable function.

How about this?

actually, I want to make radar chart using this table.

Thanks,

[Quy_Nguyen]

What i meant to say is Qlik Sense doent support you to load that kind of excel file. But yes, you can eliminate header level 1 then load. After that you need mapping header level 2 to get header level 1.

That's all i am trying to show through the example above.

[Energetic]

Thank you so much.

I appreciate your kind help.

[Quy_Nguyen]

You are welcome. Just try your data with the example above.

If you face any issue, post it here so i and other friends can help you.