Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Not applicable

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

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

1 Solution

Accepted Solutions
jolivares
Valued Contributor

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

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

8 Replies
jolivares
Valued Contributor

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

Attach the QVW directly and ask your questions...

Not applicable

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

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

Alex

jolivares
Valued Contributor

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

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

Not applicable

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

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
Valued Contributor

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

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

Not applicable

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

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
Valued Contributor

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

Thanks...

Regards from Dominican Republic

Not applicable

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

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