topic Re: Crosstable Blanks in QlikView

Crosstable Blanks

kevbrown — Thu, 12 May 2016 07:36:40 GMT

I have an excel crosstable that is half populated. Any unpopulated cells I don't want to bring into Qlikview. Is there a way of excluding them and only bringing in populated cells. I've tried where Column 2 is > 0 but then that doesn't work for column 3,4,5 etc

Re: Crosstable Blanks

avinashelite — Thu, 12 May 2016 07:37:54 GMT

please share the same data ...

Re: Crosstable Blanks

kevbrown — Thu, 12 May 2016 07:44:53 GMT

I can't seem to add an attachment

Re: Crosstable Blanks

avinashelite — Thu, 12 May 2016 07:48:00 GMT

1. Click on reply

2. At the right top you will see an option "Use Advanced editor" click on it

3.Now at the bottom right you will see the option "attach"

Re: Crosstable Blanks

kevbrown — Thu, 12 May 2016 07:58:29 GMT

Thanks

Re: Crosstable Blanks

MK_QSL — Thu, 12 May 2016 08:02:51 GMT

CrossTable(Type,Value)

LOAD F1,

[Lieder 1],

[Lieder 2],

[Line 3],

[Line 4],

[Line 5],

[Line 6],

[Line 7],

[Line 8],

[Line 9],

[LR Traywash],

[Line 11],

Preparation,

[L/R Packing],

[H/R Bacon Room],

[L/R Bacon Room],

[H/R Salad Wash],

[L/R Salad Wash],

[Bread Area],

[Bay 6 & Sanitiser],

Despatch,

Hygiene

FROM

Example.xls

(biff, embedded labels, table is Sun$);

Re: Crosstable Blanks

avinashelite — Thu, 12 May 2016 09:11:25 GMT

to eliminate the null records add one more step like this

Temp:

CrossTable(Type,Value)

LOAD F1,

[Lieder 1],

[Lieder 2],

[Line 3],

[Line 4],

[Line 5],

[Line 6],

[Line 7],

[Line 8],

[Line 9],

[LR Traywash],

[Line 11],

Preparation,

[L/R Packing],

[H/R Bacon Room],

[L/R Bacon Room],

[H/R Salad Wash],

[L/R Salad Wash],

[Bread Area],

[Bay 6 & Sanitiser],

Despatch,

Hygiene

FROM

Example.xls

(biff, embedded labels, table is Sun$);

NOCONCATENATE

Result:

LOAD *

resident

Temp

where len(trim(Value))>0;

Drop table Temp;

Crosstable Blanks