counting of rows before and after crosstable load

[s10157754]

Dear Qlikview Experts,

I had been stuck here for quite some time. Basically I have used a crosstable load in my script, then I realized the number of rows before and after crosstable load is different!

So before loading into Qlikview, I have 16 rows of data contained in txt file.

But after loading into Qlikview using crosstable, I have 92 rows of data in data model. My loading script is pasted below:

So my question is:

How can I count the total number of rows if all 4 fields (@12, @13, @14 & @15) contained null value over the total number of rows before loading into Qlikview? e.g. 2/16

Is there any way that I can achieve this outcome?

Attached sample qvw file and txt file for your reference.

Thank you for your time in advance!

Best Regards

QianNing

[olivierrobin · Accepted solution]

hello

why don't you :

load your table without cross options

use noofrows() to store the number of rows in a variable

cross load from resident table, include the variable in load and create a new field  with a conditional expression based on null values of you fields

t1;

load * from ....

let noofrecs=noofrows('t1');

t2:

crosstable

load .....

$(noofrecs) as total_rows,

if(isnull(@12) and .... isnull(@15),1,0) as flag_allnull

[s10157754]

Dear Olivier,

Thank you for your prompt reply. I need crosstable load to create some important charts which I am using now. But I would also like to create a chart in the dashboard that can count the number of null value rows / the number of total rows before loading into Qlikview.

As I was not really good at scripting, could you please provide a more detailed explanations? Thank you so much in advance!

Best Regards

Qianning

[YoussefBelloum]

Hi,

before doing the crosstable, you can know the number of nulls rows on (12, 13, 14, 15) like this:

Temp:

LOAD

     if(len(trim(@12))=0 and len(trim(@13))=0 and len(trim(@14))=0 and len(trim(@15))=0, 1) as null_rows

FROM

your_path

(txt, codepage is 1252, no labels, delimiter is ';', msq);

[marcus_sommer]

It's not quite clear from your screenshots how the real input-data and also the output-data should look like. Both loop-approaches here - the crosstable-load and the subfield-function - will increase the number of records which is usually the aim by using those features.

I your case I assume that you need only one of it which is probably the subfield(). I suggest that you comment the crosstable-statement and also 3 of the 4 subfield-statements and than looking on the results if it matched your expection - if yes take the next step and look again.

- Marcus

[s10157754]

Dear Olivier,

I got my solution from your suggestions! Thank you very much!

Best Regards

Qianning

[s10157754]

Dear Belloum,

Thank you for your suggestion. May I also know how can I count the number of rows if any field (@12, @13, @14, @15) contains data?

Best Regards

QianNing

[YoussefBelloum]

if you want the number of rows if ANY field contains data, try this:

=if(len(trim(@12))>0 OR len(trim(@13))>0 OR len(trim(@14))>0 OR len(trim(@15))>0, 1) as null_rows

[olivierrobin]

1 -load the table

t1:

load * from testing.txt (same syntax as yours)

2 - store the number of rows

let totalnbr=noofrows('t1');

3 - crosstable

same as yours except

resident t1 instead of from ...

and add new field définitions in the load

$(noofrecs) as total_rows,

if(isnull(@12) and .... isnull(@15),1,0) as flag_allnull

[Anonymous]

because crosstable will generate unqiue field value combination that is why your row count will not be equal to the number of records of the actual data.

im assuming this a machine or eqpt log does, you can aggregate disctinct count by Time.

should give you unique machine logs

=count(DISTINCT Time) =16