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

[#1]

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

[jolivares]

Attach the QVW directly and ask your questions...

[#3]

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

Alex

[jolivares]

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

[#5]

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

[jolivares · Accepted solution]

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

[#7]

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

[jolivares]

Thanks...

Regards from Dominican Republic

[#9]

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