topic Re: Question: count non-empty (xls) fields from 3 columns in QlikView

Question: count non-empty (xls) fields from 3 columns

Mon, 15 Jul 2013 21:02:44 GMT

Hello and thank you for reading my question.

I am a newbie to Qlikview but love it already. I have followed some courses on youtube but I am running into a problem.

In XLS I have the following columns (see the word document for the exact date):

{simplified}

A (Customer)

B (exp_1_class_1) let's assume some classifications are given in XLS: {green 2 times - white 2 times}

C (exp_2_class_1) and {blue: 2 times - white 2 times}

D (exp_3_class_1) all empty...

There are eg 20 customers and the classification is one out of 5 options)...{green - blue - yellow - white - black}

So, I have managed to create a count statement. Count (exp_1_class_1)+Count (exp_2_class_1)+Count (exp_3_class_1) and it does count the total rows (all clients * 3) but...

1) it also counts empty cells [Solved by Jolivares in his reply: Jul 16, 2013 11:21 AM]

2) since i have defined the graph with 'Used Dimension': exp_1_class_1, it will never show 'blue' since that only happened in the other 'dimension'.

So what I need to try to get it to do, is to consider a new 'exp_4' which is a long list of the exp_1 + exp_2 + exp_3 and it should not count the empty cells.

Something that seemed to be piece of cake is already a challenge in my first attempt!

Thank you very much for your help.

Alex

Re: Question: count non-empty (xls) fields from 3 columns

jolivares — Mon, 15 Jul 2013 21:11:20 GMT

Attach the QVW directly and ask your questions...

Re: Question: count non-empty (xls) fields from 3 columns

Mon, 15 Jul 2013 21:22:20 GMT

Thank you for your quick reply. I have added the QVW and XLS files.

Alex

Re: Question: count non-empty (xls) fields from 3 columns

jolivares — Tue, 16 Jul 2013 15:21:56 GMT

Try This... before the Load statement wirte NULLASVALUE *;

Then to avoid QV count "blank" values try in the expression:

Count(If(Len(exp_1_class_1)>0,exp_1_class_1))

Re: Question: count non-empty (xls) fields from 3 columns

Tue, 16 Jul 2013 15:32:05 GMT

Hi Jolivares!

Thank you for this solution!!! It works perfectly and avoids the empty cells. Great!

So only one things remains now. How can QV see the new 'colors' in the second or third column and add them to the one graph...?

(In my initial question marked as: 2)?

Again thanks for helping me taking this hurdle!

Alex

Re: Question: count non-empty (xls) fields from 3 columns

jolivares — Tue, 16 Jul 2013 16:07:56 GMT

Ok... you can create another table using the crosstable utility:

NULLASVALUE *;

T1:

LOAD *

FROM

(ooxml, embedded labels, table is Blad1);

T2:

CrossTable(Exp_Class, Data, 2)

LOAD * FROM

C:\Users\jolivares.GPTQ\Downloads\xls.xlsx

(ooxml, embedded labels, table is Blad1);

Drop Table T1;

Now you have a table converted to a "list"... see if this solve your problem

Re: Question: count non-empty (xls) fields from 3 columns

Thu, 18 Jul 2013 22:23:15 GMT

Hello Jolivares,

Sorry it took me some time, but thank you once more. Perfect. I had to learn a couple of hours what really happens and tried to understand what you had suggested but I have it all running smoothly.

Just need some more finetuning on the empty cells, but that might be just something like combining your two answers to which I hope I am able now!

Best regards from The Netherlands and a deep bow to you..

Alex

Re: Question: count non-empty (xls) fields from 3 columns

jolivares — Fri, 19 Jul 2013 01:13:15 GMT

Thanks...

Regards from Dominican Republic

Re: Question: count non-empty (xls) fields from 3 columns

Tue, 30 Jul 2013 18:34:08 GMT

Hi Jolivares,

I am almost there... But am having some problem in the latter phase. Is there any way you can email me so I can send you a private msg?

Please email me on singlesingon2013@aksy.nl.

Hope to hear from you. (And it won't be too much of a hasstle, I promise!)

Alex