topic Re: Excel transform using CrossTable in App Development

Excel transform using CrossTable

Energetic — Mon, 27 Aug 2018 08:29:26 GMT

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

Re: Excel transform using CrossTable

Quy_Nguyen — Mon, 27 Aug 2018 09:35:01 GMT

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:

Re: Excel transform using CrossTable

Energetic — Tue, 28 Aug 2018 05:44:33 GMT

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,

Re: Excel transform using CrossTable

Quy_Nguyen — Tue, 28 Aug 2018 11:21:29 GMT

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.

Re: Excel transform using CrossTable

Energetic — Tue, 28 Aug 2018 14:50:45 GMT

Thank you so much.

I appreciate your kind help.

Re: Excel transform using CrossTable

Quy_Nguyen — Tue, 28 Aug 2018 14:54:43 GMT

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.